TSQL Help

Question

TSQL Help

sks_989

SSC Eights!

Points: 843
More actions
January 6, 2020 at 5:50 pm

#3712756

I need help on SQL Query i have table with 2 column
Acol BCol
1 ABD
1 ACD
1 ADD
2 DEF
2 DDD
2 EEE
I want output as
1 ABD,ACD,ADD
2 DEF,DDD,EEE
Appreciate your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18387 More actions · Answer 1

;WITH cte AS
(
SELECT * 
  FROM (VALUES (1,'ABD'),
               (1,'ACD'),
               (1,'ADD'),
               (2,'DEF'),
               (2,'DDD'),
               (2,'EEE')) T(Id,Val)
)
,cte2 AS (
    SELECT DISTINCT Id
      FROM cte
)
SELECT id, x.a
  FROM cte2 a
 CROSS APPLY (VALUES (STUFF((SELECT ',' + Val
                               FROM cte b
                              WHERE b.Id = a.id
                                FOR XML PATH('')), 1, 1, ''))) x(a)

sks_989 SSC Eights! Points: 843 More actions · Answer 2

Thanks for your help. Appreciate it work as desired.

drew.allen SSC Guru Points: 77004 More actions · Answer 3

Since you've posted this in the SQL-2019 forum, you can use STRING_AGG() (introduced in SQL-2017).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18387 More actions · Answer 4

drew.allen wrote:

Since you've posted this in the SQL-2019 forum, you can use STRING_AGG() (introduced in SQL-2017).
Drew

Yes, good point

;WITH cte AS
(
SELECT * 
  FROM (VALUES (1,'ABD'),
               (1,'ACD'),
               (1,'ADD'),
               (2,'DEF'),
               (2,'DDD'),
               (2,'EEE')) T(Id,Val)
)
SELECT Id, STRING_AGG(Val,',')
  FROM cte 
 GROUP BY Id