时间戳

首页 > 解决方案 > SQL Pivot 多列而不强制聚合

sql - SQL Pivot 多列而不强制聚合

问题描述

我需要转出一些非规范化的数据，但它会重复，所以我需要它转出列，然后返回多行。

我有一张这样的桌子

INSERT #TheTable
VALUES 
        ('StockCode'    ,'a'),
        ('Warehouse'    ,'b'),
        ('TrnYear'  ,'c'),
        ('TrnMonth' ,'d'),
        ('EntryDate'    ,'e'),
        ('TrnTime'  ,'f'),
        ('StockCode'    ,'1'),
        ('Warehouse'    ,'2'),
        ('TrnYear'  ,'3'),
        ('TrnMonth' ,'4'),
        ('EntryDate'    ,'5'),
        ('TrnTime'  ,'6')

但是当我旋转它时，它只返回一行：

SELECT  StockCode,
        Warehouse,
        TrnYear,
        TrnMonth,
        TrnTime,
        EntryDate        
FROM    #TheTable AS src 
PIVOT   (MAX(column_value)
         FOR COLUMN_NAME in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])) AS piv

结果：

StockCode   Warehouse   TrnYear TrnMonth    TrnTime EntryDate   
-------------------------------------------------------------   
a           b           c       d           f       e

但我需要它返回

StockCode   Warehouse   TrnYear TrnMonth    TrnTime EntryDate   
-------------------------------------------------------------   
a           b           c       d           f       e           
1           2           3       4           5       6

标签： sqlsql-serverpivotaggregate-functionswindow-functions

解决方案

您可以先使用窗口函数，然后再使用条件聚合：

select 
    max(case when column_name = 'StockCode' then column_value end) StockCode,
    max(case when column_name = 'Warehouse' then column_value end) Warehouse,
    max(case when column_name = 'TrnYear'   then column_value end) TrnYear,
    max(case when column_name = 'TrnMonth'  then column_value end) TrnMonth,
    max(case when column_name = 'TrnTime'   then column_value end) TrnTime,
    max(case when column_name = 'EntryDate' then column_value end) EntryDate
from (
    select t.*,
        row_number() over(partition by column_name order by column_value) rn
    from #TheTable t
) t
group by rn

推荐阅读