CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
ssokol91
ssokol91

Reputation: 562

How to concatenate columns from different rows using Group By

I am trying to concatenate 3 columns from 3 different rows into one row grouping by ID

ID  Col1    Col2    Col3
5   NULL    Test2   NULL
5   Test1   NULL    NULL
5   NULL    NULL    Test3

Make it into

ID  FinalColumn
5   Test1, Test2, Test3

Thanks!!!

p.s. Values do not have to be in any specific sequence. The result could also be Test2, Test3, Test1

Upvotes: 1

Views: 75

Answers (1)

AHiggins
AHiggins

Reputation: 7227

SELECT ID, ISNULL(MAX(Col1),'') +','+ ISNULL(MAX(Col2),'') +','+ ISNULL(MAX(Col3),'') AS FinalColumn
FROM tbl
GROUP BY ID

EDIT: updated to account for the potential of NULL values in any (or all) fields: I'm probably overthinking this but it might help. Code sample below with my thoughts, feel free to ignore if you know you don't have NULLs. 

DECLARE @tbl TABLE (ID INT IDENTITY(1,1), Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 VARCHAR(20))
INSERT @tbl --(Col1, Col2, Col3)
values 
(NULL, NULL, NULL),
(NULL, '2', '3'),
('1', '2', '3'),
('4', '5', NULL),
('1', NULL, NULL);

SELECT  
    ID, 
    LEFT(
        ISNULL(MAX(Col1 + ','),'') + ISNULL(MAX(Col2 +','),'')+ ISNULL(MAX(Col3 + ','),''), 
        ISNULL(NULLIF(LEN(ISNULL(MAX(Col1 + ','),'') + ISNULL(MAX(Col2 +','),'')+ ISNULL(MAX(Col3 + ','),'')),0),1) -1
        ) AS FinalColumn
FROM @tbl
GROUP BY ID

Upvotes: 4

Related Questions