How to return specific text when multiple records

Question

I am relatively new to SQL and I will try to get the terminology correct. I have 2 tables, tbl_Trans which holds the general transaction details, and tbl_TransData which holds the details of the transaction.

I want to show each Trans record and the category of the trans which is held in the tbl_TransData. If there is more than one category for each Trans I want the text to be 'Mulitple', otherwise to return the category field

TRANS table content

int_Trans_ID    dtm_TransDate   txt_Type    txt_Description txt_Bank dbl_Amount
1               17/12/2018      REC         Sales           Current 1000
2               20/12/2018      PAY         Expenses paid   Current -155
3               21/12/2018      PAY         MW Repairs      Current -250

TRANSDATA table content

int_TransData_ID    int_TransID txt_Category    dbl_Amount
1                   1           Sales A         -600
2                   1           Sales B         -400
3                   2           Travel          100
4                   2           Meal            55
5                   3           MW Repairs      250

This is the code so far, but if I replace the ELSE 'Single' with ELSE txt_Category it does not work.

SELECT
int_Trans_ID,
dtm_TransDate AS Date,
txt_Type AS Type,
txt_Description AS Description,
    (SELECT 
            CASE
                WHEN count(int_TransID) > 1
                THEN 'Multiple'
                ELSE 'Single'
            END
    FROM
        dbo.tbl_TransData TD
    WHERE
        TD.int_TransID = T.int_Trans_ID) AS Category
FROM
   tbl_Trans T
GROUP BY
   int_Trans_ID, dtm_TransDate, txt_Type, txt_Description

This is what I would like to see.

int_Trans_ID    Date        Type    Description   Category
1               2018-12-17  REC     Sales         Multiple
2               2018-12-20  PAY     Expenses paid Multiple
3               2018-12-21  PAY     Car Repairs   MW Repairs

Sorry for the format of the tables.

Any help would be much appreciated, or even pointing me in the direction of other posts.

Answer 1

I would do:

with
x as (
  select ins_trans_id, count(*) as cnt, max(txt_category) as cat 
  from transdata
  group by ins_trans_id
)
select
  t.int_trans_id,
  t.dtm_transdate,
  t.txt_type,
  t.txt_description,
  case when x.cnt = 0 then 'no category'
       when x.cnt = 1 then x.cat
       else 'Multiple' end as Category
from trans t
left join x on x.int_trans_id = t.int_trans_id

Answer 2

Please have a look at this, I wrote subquery for case condition

SELECT
  int_Trans_ID,
  dtm_TransDate,
  txt_type,
  txt_Description,
  CASE WHEN (SELECT COUNT(*) FROM tbl_Trans_Data td WHERE td.int_Trans_ID = t.int_Trans_ID)) > 1 THEN
    'MULTİPLE'
  ELSE 
    (SELECT Category from tbl_trans_data td Where td.int_Trans_ID = t.int_Trans_ID)
  END AS Category
FROM
  tbl_Trans t