So I have the simple base query below.

select
TransNo as ‘Transaction No.’,
Status as ‘Status’,
CliNam as ‘Account Holder Name’,
PayType as ‘Receipt Type’,
Amount/100 AS ‘Total’,
InputDateTime as ‘Amount Input date’,
InputBy as ‘Input by’,
AuthorisedBy As ‘Authorised By’,
ReleasedBy As ‘Released By’
from Single_Queues
order by InputDateTime asc

However I need to add the below in to form an additional column in the above query.

select
STUFF((
SELECT ‘ ‘ + CAST([Comment] AS VARCHAR(MAX))
FROM Single_Queues_Comments I
WHERE (I.TransNo = O.TransNo) and I.CommentNo = 1
FOR XML PATH(”),TYPE).value(‘(./text())[1]’,’VARCHAR(MAX)’)
,1,1,”) as ‘Full Name’
from
Single_Queues_Comments O
group by
TransNo

How can this be done?

I tried the below but with no joy.

select
TransNo as ‘Transaction No.’,
Status as ‘Status’,
CliNam as ‘Account Holder Name’,
(select
STUFF((
SELECT ‘ ‘ + CAST([Comment] AS VARCHAR(MAX))
FROM Single_Queues_Comments I
WHERE (I.TransNo = O.TransNo) and I.CommentNo = 1
FOR XML PATH(”),TYPE).value(‘(./text())[1]’,’VARCHAR(MAX)’)
,1,1,”)
from
Single_Queues_Comments O
group by
TransNo) as ‘Full Name’,
PayType as ‘Receipt Type’,
Amount/100 AS ‘Total’,
InputDateTime as ‘Amount Input date’,
InputBy as ‘Input by’,
AuthorisedBy As ‘Authorised By’,
ReleasedBy As ‘Released By’
from Single_Queues
order by InputDateTime asc