sqlserver一个动态交叉表的范例

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

--建立测试环境
setnocounton
createtabletest(modelvarchar(20),dateint,qtyint)
insertintotestselect'a','8','10'
insertintotestselect'a','10','50'
insertintotestselect'b','8','100'
insertintotestselect'b','9','200'
insertintotestselect'b','10','100'
insertintotestselect'c','10','200'
insertintotestselect'd','10','300'
insertintotestselect'e','11','250'
insertintotestselect'e','12','100'
insertintotestselect'f','12','150'
go
--测试

declare@sqlvarchar(8000)
set@sql='selectmodel,'
select@sql=@sql+'sum(casewhendate='''+cast(dateasvarchar(10))+'''thenqtyelse0end)['+cast(dateasvarchar(10))+'],'
from(selectdistincttop100percentdate
fromtestorderbydate)a

set@sql=left(@sql,len(@sql)-1)+'fromtestgroupbymodel'

exec(@sql)



--删除测试环境
droptabletest
setnocountoff

/**//*
model89101112
---------------------------------------------------------------------------
a1005000
b10020010000
c0020000
d0030000
e000250100
f0000150
*/