sql - 缓存 SQL 查询以从多条记录中创建 1 行

Question

我有以下记录并想创建 1 行记录。我尝试了 STUFF，FOR XML PATH 并没有工作

+-----------+-------+---------+
| CLIENT_ID | Event | DX_Code |
+-----------+-------+---------+
|        54 |     5 | F45.72  |
|        54 |     5 | X45.34  |
|        54 |     5 | M98.32  |
+-----------+-------+---------+

输出 = 54, 5, F45.72 X45.34 M98.32

标签： sqlsql-servercachingmultiple-columnscomma

Answer 1

您可以使用STUFF()with FOR XML PATH('')as来做到这一点

CREATE TABLE T
    ([CLIENT_ID] int, [Event] int, [DX_Code] varchar(6))
;

INSERT INTO T
    ([CLIENT_ID], [Event], [DX_Code])
VALUES
    (54, 5, 'F45.72'),
    (54, 5, 'X45.34'),
    (54, 5, 'M98.32')
;

SELECT DISTINCT T1.[CLIENT_ID],
                T1.[Event],
                STUFF(
                        (
                          SELECT ',' + T2.[DX_Code] 
                          FROM T T2
                          WHERE T2.[CLIENT_ID] = T1.[CLIENT_ID]
                                AND T2.[Event] = T1.[Event]
                          FOR XML PATH ('')
                        ) , 1, 1, ''
                    ) Result
FROM T T1;