CODEWITHSUNDEEP
sqlsql-servert-sqlstring-aggregationfor-xml-path
Arsee
Arsee

Reputation: 671

How to concatenate multiple rows

I have an SQL Server script that concatenates multiple row based on unique ID. I need to be able to combine them into 1 row to avoid showing multiple lines for the same unique ID. The code below is not working. Please advise. Thanks.

Line of code

select
  STUFF((
    SELECT DISTINCT ', ' + CAST(Transcript AS VARCHAR(max)) [text()]
    FROM MyTableName
    FOR XML PATH(''), TYPE) 
.value('.','VARCHAR(MAX)'),1,2,'') Transcript_Combined from #Transcripts

enter image description here

Upvotes: 0

Views: 80

Answers (1)

Juan Midence
Juan Midence

Reputation: 36

You can obtain the result desired using the following statement (I added the temporal table to have example data):

declare @transcripts table (
    id int,
    transcript varchar(500)
)

insert into @transcripts
values(1, 'Some string'),
(1, 'Another String'),
(2, 'Some other string'),
(2, 'More string'),
(3, 'One more time string')

select distinct a.id,
    stuff(b.transcripts_combined, 1, 2, '') transcripts_combined
from @transcripts a
outer apply (
    select ', ' + x.transcript
    from @transcripts x
    where x.id = a.id
    for xml path('')
) b([transcripts_combined])

Upvotes: 0

Related Questions