sqlserver一个动态交叉表的范例

社区问的人太多了，保存一个备用

-- 建立测试环境
set  nocount  on
create   table  test(model  varchar ( 20 ),date  int  ,qty  int )
insert   into  test  select   ' a ' , ' 8 ' , ' 10 '
insert   into  test  select   ' a ' , ' 10 ' , ' 50 '
insert   into  test  select   ' b ' , ' 8 ' , ' 100 '
insert   into  test  select   ' b ' , ' 9 ' , ' 200 '
insert   into  test  select   ' b ' , ' 10 ' , ' 100 '
insert   into  test  select   ' c ' , ' 10 ' , ' 200 '
insert   into  test  select   ' d ' , ' 10 ' , ' 300 '
insert   into  test  select   ' e ' , ' 11 ' , ' 250 '
insert   into  test  select   ' e ' , ' 12 ' , ' 100 '
insert   into  test  select   ' f ' , ' 12 ' , ' 150 '
go
-- 测试

declare   @sql   varchar ( 8000 )
set   @sql = ' select model, '
  select   @sql = @sql + ' sum(case when date= ''' + cast (date  as   varchar ( 10 )) + '''  then qty else 0 end)[ ' + cast (date  as   varchar ( 10 )) + ' ], '
from  ( select   distinct   top   100   percent   date
  from  test  order   by  date)a

set   @sql   = left ( @sql , len ( @sql ) - 1 ) + '  from test group by model '

exec ( @sql )

 

-- 删除测试环境
drop   table  test
  set  nocount  off

/**/ /*
model                8           9           10          11          12
-------------------- ----------- ----------- ----------- ----------- -----------
a                    10          0           50          0           0
b                    100         200         100         0           0
c                    0           0           200         0           0
d                    0           0           300         0           0
e                    0           0           0           250         100
f                    0           0           0           0           150
*/

 

转载于:https://www.cnblogs.com/cl1024cl/archive/2007/12/11/6204977.html