SQL行转列(Pivoting)处理列名场景下的查询方案优化咨询

需求说明 #

• 当前查询输出的结果：
• 期望转换后的输出格式：
  用户已通过多表自关联的方式实现需求，希望获取更优的实现方案。

现有实现代码 #

select distinct
    t1.Area,
    t2.MedianCurrentYear,
    t2.MedianPreviouYear,
    t2.Difference,
    t3.MedianCurrentYear,
    t3.MedianPreviouYear,
    t3.Difference,
    t4.MedianCurrentYear,
    t4.MedianPreviouYear,
    t4.Difference
 from #temp as t1
 left join #temp as t2 on t1.Area = T2.Area and T2.NumberOfBedrooms = 2
 left join #temp as t3 on t1.Area = T3.Area and T3.NumberOfBedrooms = 3
 left join #temp as t4 on t1.Area = T4.Area and T4.NumberOfBedrooms = 4

样例测试数据 #

Create Table #temp
(
    Area varchar(50),
    NumberOfBedrooms int,
    MedianCurrentYear money,
    MedianPreviouYear money,
    Difference money
)

insert into #temp
(
    Area,
    NumberOfBedrooms,
    MedianCurrentYear,
    MedianPreviouYear,
    Difference 
)
select
    'Area1',
    2,  
    370,
    365,
    5
union all
select
    'Area1',    
    3,
    406,
    408,    
    -2
union all
select
    'Area1',
    4,
    520,    
    520,
    0
union all
select
    'Area2',    
    2,
    300,
    280,    
    20
union all
select
    'Area2',
    3,  
    406,    
    408,
    -2
union all
select
    'Area2',    
    4,  
    520,    
    520,
    0

更优实现方案 #

推荐使用SQL原生的PIVOT行转列语法实现，代码更简洁、执行效率更高：

SELECT 
    Area,
    [2_MedianCurrentYear] = [2],
    [2_MedianPreviouYear] = [2_Prev],
    [2_Difference] = [2_Diff],
    [3_MedianCurrentYear] = [3],
    [3_MedianPreviouYear] = [3_Prev],
    [3_Difference] = [3_Diff],
    [4_MedianCurrentYear] = [4],
    [4_MedianPreviouYear] = [4_Prev],
    [4_Difference] = [4_Diff]
FROM (
    SELECT 
        Area,
        NumberOfBedrooms,
        val,
        col = cast(NumberOfBedrooms as varchar(10)) + suffix
    FROM #temp
    CROSS APPLY (
        VALUES 
            ('', MedianCurrentYear),
            ('_Prev', MedianPreviouYear),
            ('_Diff', Difference)
    ) v(suffix, val)
) t
PIVOT (
    MAX(val)
    FOR col IN ([2], [2_Prev], [2_Diff], [3], [3_Prev], [3_Diff], [4], [4_Prev], [4_Diff])
) pvt

方案优势 #

• 仅需扫描1次临时表，无需多次自关联，数据量越大性能优势越明显
• 后续扩展维度方便，如需新增1居室、5居室的统计，仅需调整PIVOT的列列表，不需要额外增加JOIN语句
• 无需额外加DISTINCT去重，天然每个区域对应一行结果，逻辑更清晰

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