sql - MS SQL - 加入多个枢轴

Question

我正在尝试将多个枢轴合并为一个语句。请您帮忙。

SELECT ID, A as [LglCode-A],B as [LglCode-B],C as [LglCode-C],D as [LglCode-D] from 
             (
                select ID,LglFlg, Code
                from #tmp1
            ) x
            pivot 
            (
                max(Legal)
                for Code in ([A],[B],[C],[D])
            ) p; 

SELECT ID, A as [MH Code-A],B as [MH Code-B],C as [MH Code-C],D as [MH Code-D] from 
             (
                select ID,[MH Code], Code
                from #tmp1
            ) x
            pivot 
            (
                max([MH Code])
                for Code in ([A],[B],[C],[D])
            ) p; 

我从链接中看到了这种方法，这是唯一更好的方法吗？ MS SQL 多次透视另一个表

标签： sqlsql-serverdatabasepivot

Answer 1

我会建议条件聚合。我认为这可以满足您的要求：

select id,
       max(case when Code = 'A' then lglflg end) as legal_a,
       max(case when Code = 'B' then lglflg end) as legal_b,
       max(case when Code = 'C' then lglflg end) as legal_c,
       max(case when Code = 'D' then lglflg end) as legal_d,
       max(case when Code = 'A' then [MH Code] end) as mh_a,
       max(case when Code = 'B' then [MH Code] end) as mh_b,
       max(case when Code = 'C' then [MH Code] end) as mh_c,
       max(case when Code = 'D' then [MH Code] end) as mh_d
from #tmp1
group by id;