我有一个非常艰难的时刻试图找出如何在具有多个列的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
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函数与动态代码的组合？
我试着按照这里的例子：
但我最终得到了奇怪的结果.
解决方法
为了获得结果,您需要先查看“总量”和“体积”列中的数据,然后再应用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 | +-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
