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


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

--建立测试环境
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
*/
本文作者:



相关阅读:
Wscript.Shell 对象详细介绍!!特好的东西
CSS hack浏览器兼容一览表
关于SQL Server中几个未公布的访问注册表的扩展存储过程
asp伪静态情况下实现的utf-8文件缓存实现代码
零基础学习JavaScript(1)-1.1什么是JavaScript
PHP中全面阻止SQL注入式攻击分析小结
data guard的三种模式
vbs 字符统计功能模块
PHP中不能使用exec(),system(),shell_system()等函数
电脑的离开模式,如何开启电脑的离开模式
js 实用的无间断滚动图效果(良好兼容性)
MM.MySQLMySQL的JDBC驱动程序的介绍
asp.net GridView控件中实现全选的解决方案
W3C教程(8):W3C XML Schema 活动
快速导航

Copyright © 2016 phpStudy |