How to concatenate columns from different rows using Group By

Question

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

AHiggins · Accepted Answer · 2014-05-23 19:02:37Z

4

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

edited May 23, 2014 at 19:02

answered May 23, 2014 at 18:43

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

AHiggins Over a year ago

Updated the query to correct a mistake in the GROUP BY, can you try again?

crthompson Over a year ago

If any one of those values is ALL null, then concatinating null to string gives null.

AHiggins Over a year ago

Edited one more time, a suggested edit that I accepted actually changed the concatenation string.

crthompson Over a year ago

I'm sure we overwrote one another. I had rolled back that edit because it was nonsense.

AHiggins Over a year ago

That must have been it, @paqogomez. Thanks for the help! ssokol91, added in some extra NULL handling logic to account for the potential problems that paqogomez pointed out. Use it if you think you might return a NULL value in one or more of the columns: basically it moves the comma values to only be added if the value before them is NULL. The LEFT() function removes the last comma in the string (in case Col3 returns a NULL).

|

Collectives™ on Stack Overflow

How to concatenate columns from different rows using Group By

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related