如何使用Group By连接来自不同行的列

我试图将3个不同行的3列连接成一个按ID分组的行

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

成为

ID  FinalColumn
5   Test1, Test2, Test3

谢谢!!!

附:值不必具有任何特定顺序 . 结果也可以是Test2,Test3,Test1

回答(1)

2 years ago

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

编辑:更新以考虑任何(或所有)字段中 NULL 值的潜在可能性: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有NULL .

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