SQL Server：超过5列的动态枢轴

我有一个非常艰难的时刻试图找出如何在具有多个列的sql Server 2008中进行动态枢轴.

我的样品表如下：

ID  YEAR  TYPE  TOTAL   VOLUME
DD1 2008    A   1000    10
DD1 2008    B   2000    20
DD1 2008    C   3000    30
DD1 2009    A   4000    40
DD1 2009    B   5000    50
DD1 2009    C   6000    60
DD2 2008    A   7000    70
DD2 2008    B   8000    80
DD2 2008    C   9000    90
DD2 2009    A   10000   100
DD2 2009    B   11000   110
DD2 2009    C   12000   120

我正在尝试如下的枢纽：

ID  2008_A_TOTAL    2008_A_VOLUME   2008_B_TOTAL    2008_B_VOLUME   2008_C_TOTAL    2008_C_VOLUME   2009_A_TOTAL    2009_A_VOLUME   2009_B_TOTAL    2009_B_VOLUME   2009_C_TOTAL    2009_C_VOLUME
DD1 1000            10              2000            20              3000            30              4000            40              5000            50              6000            60
DD2 7000            70              8000            80              9000            90              10000           100             11000           110             12000           120

我的sql Server 2008查询如下创建表：

CREATE TABLE ATM_TRANSACTIONS 
(
 ID varchar(5),T_YEAR varchar(4),T_TYPE varchar(3),TOTAL int,VOLUME int
);
 
INSERT INTO ATM_TRANSACTIONS
(ID,T_YEAR,T_TYPE,TOTAL,VOLUME)
 
VALUES
('DD1','2008','A',1000,10),('DD1','B',2000,20),'C',3000,30),'2009',4000,40),5000,50),6000,60),('DD2',7000,70),8000,80),9000,90),10000,100),11000,110),1200,120);

T_Year列可能会在将来更改,但T_TYPE列通常是知道的,所以我不知道我是否可以使用sql Server中的PIVOT函数与动态代码的组合？

我试着按照这里的例子：

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

但我最终得到了奇怪的结果.

解决方法

为了获得结果,您需要先查看“总量”和“体积”列中的数据,然后再应用PIVOT函数才能获得最终结果.我的建议是首先写一个硬编码版本的查询,然后将其转换为动态sql.

UNPIVOT进程将这些多列转换成行.有几种方法可以使用UNPIVOT,您可以使用UNPIVOT功能,也可以使用CROSS APPLY.不透明数据的代码将类似于：

select id,col = cast(t_year as varchar(4))+'_'+t_type+'_'+col,value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total',total union all
    select 'volume',volume
) c (col,value);

这给你的数据格式如下：

+-----+---------------+-------+
| id  |      col      | value |
+-----+---------------+-------+
| DD1 | 2008_A_total  |  1000 |
| DD1 | 2008_A_volume |    10 |
| DD1 | 2008_B_total  |  2000 |
| DD1 | 2008_B_volume |    20 |
| DD1 | 2008_C_total  |  3000 |
| DD1 | 2008_C_volume |    30 |
+-----+---------------+-------+

然后你可以应用PIVOT功能：

select ID,[2008_A_total],[2008_A_volume],[2008_B_total],[2008_B_volume],[2008_C_total],[2008_C_volume],[2009_A_total],[2009_A_volume]
from
(
    select id,value
    from ATM_TRANSACTIONS t
    cross apply
    (
        select 'total',total union all
        select 'volume',volume
    ) c (col,value)
) d
pivot
(
    max(value)
    for col in ([2008_A_total],[2009_A_volume])
) piv;

现在您具有正确的逻辑,您可以将其转换为动态sql：

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+t_type+'_'+col) 
                    from ATM_TRANSACTIONS t
                    cross apply
                    (
                        select 'total',1 union all
                        select 'volume',2
                    ) c (col,so)
                    group by col,so,T_YEAR
                    order by T_YEAR,so
            FOR XML PATH(''),TYPE
            ).value('.','NVARCHAR(MAX)'),1,'')
 
set @query = 'SELECT id,' + @cols + ' 
            from 
            (
                select id,col = cast(t_year as varchar(4))+''_''+t_type+''_''+col,value
                from ATM_TRANSACTIONS t
                cross apply
                (
                    select ''total'',total union all
                    select ''volume'',volume
                ) c (col,value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '
 
execute sp_executesql @query;

这将给你一个结果：

+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| id  | 2008_A_total | 2008_A_volume | 2008_B_total | 2008_B_volume | 2008_C_total | 2008_C_volume | 2009_A_total | 2009_A_volume | 2009_B_total | 2009_B_volume | 2009_C_total | 2009_C_volume |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| DD1 |         1000 |            10 |         2000 |            20 |         3000 |            30 |         4000 |            40 |         5000 |            50 |         6000 |            60 |
| DD2 |         7000 |            70 |         8000 |            80 |         9000 |            90 |        10000 |           100 |        11000 |           110 |         1200 |           120 |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+

SQL Server：超过5列的动态枢轴

解决方法

猜你在找的MsSQL相关文章