How to get strings from multiple rows into 1 result?

Transact-SQL
1

Hello all,

I need help on how to get strings in multiple rows into 1 row. Below are data examples.

Acct# Comment# CommentSeq CommentText
123456 1 1 Loan is active.
123456 2 1 Loan is in default. Customer call to request for ext
123456 2 2 ension on loan payment.
123456 3 1 Call customer. No answer.
123456 4 1 Need to call home owner to discuss what is the ne
123456 4 2 xt step but there is no contact number to call, there
123456 4 3 is also no email listed, no secondary phone # too.

So the result for the sample from above should have the following:

Acct# Comment Text
123456 Loan is active.
123456 Loan is in default. Customer call to request for extension on loan payment.
123456 Call customer. No answer.
123456 Need to call home owner to discuss what is the next step but there is no contact number to call, there is also no email listed, no secondary phone # too.

Please help. Thank you very much for all your help.

2

try this
create table #comment
(
Acct# varchar(6),
Comment# int,
CmtSeq int,
CommentTxt varchar(100)

)


insert into #comment
values
('123456' ,1 ,1 ,'Loan is active.'),
('123456',2, 1, 'Loan is in default. Customer call to request for ext'),
('123456', 2, 2, 'ension on loan payment.'),
('123456' ,3 ,1, 'Call customer. No answer.'),
('123456', 4, 1, 'Need to call home owner to discuss what is the ne'),
('123456', 4, 2, 'xt step but there is no contact number to call, there'),
('123456', 4, 3, 'is also no email listed, no secondary phone # too.')


with cte
as
(
SELECT t.Acct#, t.comment#,
COALESCE(
STUFF(
(SELECT ' ' + CAST(c.CommentTxt AS VARCHAR(100)) AS [text()]
FROM #comment AS c
WHERE c.comment#= t.comment#
ORDER BY c.Acct#
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
1, 1, ''),
'') AS TextComents
FROM #comment AS t
)
select distinct  x.* from cte as x