ms sql-连接多个数据透视

anauzrmj 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(278)

我正在尝试将多个数据透视合并为一个语句。请您帮助我如何合并。

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多次透视另一个表

sql sql-server Database pivot

来源：https://stackoverflow.com/questions/62719307/ms-sql-join-multiple-pivots

2条答案

按热度按时间

wrrgggsh1#

我建议条件聚合。我想这正是你想要的：

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;

赞(0）回复(0）举报 2021-07-29

tcbh2hod2#

您可以尝试以下操作：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((select distinct ', 
            MAX(CASE WHEN [Code]=''' + CAST([Code] as varchar(10)) + ''' THEN [Legal] ELSE 0 END) AS [LglCode-' + CAST([Code] as varchar(10)) + '],
            MAX(CASE WHEN [Code]=''' + CAST([Code] as varchar(10)) + ''' THEN [MH Code] ELSE 0 END) AS [MH Code-' + CAST([Code] as varchar(10)) + ']'
            /*---------------You can add other columns here similarly--------------*/
            FROM #tmp1 
            FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')

SET @query = 'SELECT ID, ' + @Cols + '  FROM #tmp1 group by ID' 

--print (@query)
exec(@query)

由于这里没有可用的测试数据，您可能需要在这里或那里进行一些更改。

赞(0）回复(0）举报 2021-07-29

我来回答

ms sql-连接多个数据透视

2条答案

相关问题

热门标签

最新问答