如何使用SQL Server 2019实现指定的行转列查询结果？

源数据（customer表） #

当前尝试的SQL（未达到预期） #

DECLARE @ids IdCollection;

insert into @ids
values (12)
insert into @ids
values (13)
insert into @ids
values (14)

select *
from customer a
inner join @ids i on a.MemberID = i.Value;

期望查询结果 #

可实现预期的SQL语句 #

要实现这种行转列的效果，需要用到SQL Server的PIVOT运算符，同时为了保证所有指定的MemberID都能显示（即使没有对应101/102/103类型的数据），需要先构造指定ID与目标类型的全量组合，再关联原表数据后透视：

DECLARE @ids IdCollection;

INSERT INTO @ids VALUES (12), (13), (14);

-- 定义需要转为列的目标类型
WITH TargetTypes AS (
    SELECT '101' AS Type UNION ALL
    SELECT '102' AS Type UNION ALL
    SELECT '103' AS Type
),
-- 生成所有MemberID与目标Type的组合
MemberTypeCombination AS (
    SELECT i.Value AS MemberID, tt.Type
    FROM @ids i
    CROSS JOIN TargetTypes tt
)
SELECT 
    MemberID,
    [101], [102], [103]
FROM MemberTypeCombination m
LEFT JOIN customer c ON m.MemberID = c.MemberID AND m.Type = CAST(c.Type AS VARCHAR(10))
PIVOT (
    MAX(c.Date) FOR m.Type IN ([101], [102], [103])
) AS PivotTable
ORDER BY MemberID;

关键说明 #

1. TargetTypes 公共表表达式（CTE）明确了需要转为列的类型值；
2. MemberTypeCombination 通过CROSS JOIN生成指定MemberID与目标Type的全量组合，确保每个MemberID都能覆盖所有目标列；
3. 使用LEFT JOIN关联原表，保留所有组合记录，没有匹配数据的位置会显示null；
4. PIVOT将Type列的取值转为列名，用MAX(Date)聚合是因为每个MemberID+Type组合最多一条数据，聚合操作不影响结果；
5. 最后按MemberID排序，保证结果顺序与期望一致。

内容的提问来源于stack exchange，提问作者haha