SQL Server数据库动态交叉表的参考示例

SQL Server数据库动态交叉表的参考示例: --建立测试环境set nocount oncreate 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 in


SQL Server数据库动态交叉表的参考示例:

--建立测试环境set nocount oncreate 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)aset @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           0b                    100         200         100         0           0c                    0           0           200         0           0d                    0           0           300         0           0e                    0           0           0           250         100f                    0           0           0           0           150*/
本文作者:

郑重声明:本文版权包含图片归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们(delete@yzlfxy.com)修改或删除,多谢。

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。

留言与评论(共有 0 条评论)
昵称:
匿名发表
   
验证码: