0

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Arsee
  • 651
  • 2
  • 11
  • 36
  • 1
    *"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
  • 2
    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
  • @joelcoehoorn SSMS v18.5 – Arsee Mar 30 '23 at 17:24
  • 1
    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

1 Answers1

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