How to concatenate multiple rows

Question

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

*"The code below is not working"* What does "not working" mean? Why aren't you using `STRING_AGG`? — Thom A, Mar 30 '23 at 14:42
If i understand you correctly... usually one does something like this: select (select ... from table t where t.id = t2.id for xml) from (select distinct id from table t2 ) t2 — siggemannen, Mar 30 '23 at 15:14
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Mar 30 '23 at 15:25
What version of SQL Server do you have? Recent versions can do **WAY** better than stuff()/For Xml. — Joel Coehoorn, Mar 30 '23 at 16:27
Telling us the SSMS version is like telling us the car you drive is a Sony, because that's the name you see on the dashboard radio. Fine for some, but alarming from your mechanic. — Joel Coehoorn, Mar 30 '23 at 20:00

Juan Midence · Answer 1 · 2023-03-30T16:23:06.810

0

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])

edited Mar 30 '23 at 16:23

answered Mar 30 '23 at 16:22

Juan Midence

36
3

SQL Server eventually did implement a version of the GROUP_AGG string folding function present in other RDBMS and called it [STRING_AGG](https://stackoverflow.com/a/43664415/314291), so we don't need the FOR XML / STUFF hack. – StuartLC Mar 30 '23 at 16:28
For complete docs on STRING_AGG see: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16 – Luuk Mar 30 '23 at 16:38
@StuartLC you are right, although the function would only work on SQL Server 2017 or later https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql – Juan Midence Mar 30 '23 at 16:42
`for xml path(''), type` and `stuff(b.transcripts_combined.value(text()[1]', 'nvarchar(max)'),` would be wise, to avoid XML entitization – Charlieface Mar 30 '23 at 20:18

How to concatenate multiple rows

1 Answers1