sql-server - 选择以逗号分隔的摘要计数？

Question

SQL Server，如何选择以逗号分隔的摘要计数？
我有数据：

DECLARE @TB TABLE (ID INT, APP VARCHAR(1))
INSERT INTO @TB
VALUES
(1, 'A'),
(1, 'A'),
(1, 'B'),
(1, 'C'),
(1, 'C'),
(2, 'A'),
(2, 'B'),
(2, 'B')

我想查询这样的结果：

标签： sql-server

Answer 1

如果您使用的是 SQL Server 2017或更高版本，则可以使用 STRING_AGG 如下 -

DECLARE @TB TABLE (ID INT, APP VARCHAR(1))

INSERT INTO @TB
VALUES
(1, 'A'),
(1, 'A'),
(1, 'B'),
(1, 'C'),
(1, 'C'),
(2, 'A'),
(2, 'B'),
(2, 'B')

SELECT 
    ID, STRING_AGG (T, ',') 
FROM 
    (SELECT ID, APP + ' ' + CAST(COUNT(*) AS VARCHAR) T
     FROM @tb
     GROUP BY ID,APP) A
GROUP BY ID

对于 SQL Server 2016 或更早版本 -

在这里演示

SELECT 
    ID,  
    abc = STUFF((SELECT ',' + T 
                 FROM 
                     (SELECT ID, APP + ' ' + CAST(COUNT(*) AS VARCHAR) T
                      FROM @tb
                      GROUP BY ID, APP) t1
                 WHERE t1.id = t2.id
                 FOR XML PATH ('')), 1, 1, '') 
FROM
    @tb t2
GROUP BY 
    id;