SQL 查询以选择特定的列值并连接它们

Question

谁能帮我实现这个查询:我需要为每个值为 1 的字母携带所有 ID:

Answer 1

这是一个两步过程。首先，您需要将列转为行:

SELECT  upvt.ID, Letters
FROM    T
        UNPIVOT
        (   Value
            FOR Letters IN ([A], [B], [C], [D], [E], [F])
        ) upvt
WHERE   upvt.Value = 1;

这给出:

ID  Letters
10  A
10  C
10  E
10  F
...

然后您需要从这个结果中连接 ID:'

WITH Unpivoted AS
(   SELECT  upvt.ID, Letters
    FROM    T
            UNPIVOT
            (   Value
                FOR Letters IN ([A], [B], [C], [D], [E], [F])
            ) upvt
    WHERE   upvt.Value = 1
)
SELECT  u.Letters,
        IDs = STUFF((   SELECT  ', ' + CAST(u2.ID AS VARCHAR(10))
                        FROM    Unpivoted u2
                        WHERE   u.Letters = u2.Letters
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM    Unpivoted u
GROUP BY u.Letters;

给出:

Letters IDs
A       10, 20, 50
B       20, 40
C       10, 20, 30, 40, 50
D       30, 40
E       10, 50
F       10, 20, 40

Example on SQL Fiddle