String_agg simulation

Question

Hi I am having SQL Server 2014 and I am trying to work with text columns. I understand there is a new function STRING_AGG to concatenate multiple columns with a separator however STRING_AGG function work on versions SQL server 2017 and above. Please help me with equivalent query to achieve this. Attached is my data look like. I am expecting result like

CASE_TABLE:

CASES
--------    
CS03972981  
CS03245184  
CS04054200  
CS02808145  
CS01618494  
CS03873177

COMMENTS_TABLE:

CASES        COMMENTS   
----------   ---------  
CS03972981  ABC
CS03972981  ABC1
CS03972981  ABC2
CS03972981  ABC3
CS03245184  DEF
CS03245184  DEF1
CS03245184  DEF2
CS04054200  GHI
CS02808145  JKL
CS01618494  MNO
CS01618494  MNO1
CS01618494  MNO2
CS01618494  MNO3
CS01618494  MNO4
CS03873177  PQR

RESULT_TABLE:

CS03972981  ABC,ABC1,ABC2,ABC3
CS03245184  DEF,DEF1,DEF2
CS04054200  GHI
CS02808145  JKL
CS01618494  MNO,MNO1,MNO2,MNO3,MNO4
CS03873177  PQR

Appreciate, Krishna.

Does this answer your question? [string\_agg for sql server pre 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017) — astentx, Nov 12 '20 at 13:45

score -1 · Answer 1 · answered Nov 12 '20 at 13:45

-1

You need to use the XML method:

select c.*,
       stuff( (select ',' + co.comments
               from comments co
               where co.cases = c.cases
               for xml path('')
              ), 1, 1, ''
            ) as all_comments
from cases c;

answered Nov 12 '20 at 13:45

Gordon Linoff

1,242,037
58
646
786

Sorry for the delay, I try the same but query keep running for an hour and cancelling. I understand the comments table is huge. I try by picking couple of cases and it is working like charm. – Vamsi Nov 25 '20 at 19:39
@VamsiKrishna . . . You need an index on `comments(cases)` or `comments(cases, comments)`. – Gordon Linoff Nov 25 '20 at 23:44

String_agg simulation

1 Answers1