--建立测试环境 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)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 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 */
--建立测试环境 set nocount on create table test(model varchar(20),date datetime ,qty int) insert into test select 'a','2001-2-01','10' insert into test select 'a','2001-4-01','50' insert into test select 'b','2001-5-01','100' insert into test select 'b','2001-6-01','200' insert into test select 'b','2001-7-01','100' insert into test select 'c','2001-8-01','200' insert into test select 'd','2001-9-01','300' insert into test select 'e','2001-10-01','250' insert into test select 'e','2001-11-01','100' insert into test select 'f','2001-12-01','150' go --测试 declare @sql varchar(8000) select @sql=isnull(@sql+',','')+'sum(case when datepart(qq,date)='+ltrim(n)+' then qty else 0 end) as [qty'+cast(N as varchar(10))+']' from (select 1 as n union select 2 union all select 3 union select 4)a set @sql ='select model,sum(qty) as qty,'+@sql+' from test group by model' exec(@sql) --删除测试环境 drop table test set nocount off /* model qty qty1 qty2 qty3 qty4 -------------------- ----------- ----------- ----------- ----------- ----------- a 60 10 50 0 0 b 400 0 300 100 0 c 200 0 0 200 0 d 300 0 0 300 0 e 350 0 0 0 350 f 150 0 0 0 150 */
good good study,day day up.
use pubsSELECT isnull(stor_name,'Total') as stor_name,sum(qty) as Total, sum(case when datepart(qq,ord_date)=1 then qty else 0 end) as Qtr1, sum(case when datepart(qq,ord_date)=2 then qty else 0 end) as Qtr2, sum(case when datepart(qq,ord_date)=3 then qty else 0 end) as Qtr3, sum(case when datepart(qq,ord_date)=4 then qty else 0 end) as Qtr4 FROM stores t JOIN sales s on s.stor_id = t.stor_id WHERE year(s.ord_date) = '1993' group by stor_name with rollup /* stor_name Total Qtr1 Qtr2 Qtr3 Qtr4 ---------------------------------------- ----------- ----------- ----------- ----------- ----------- Barnum's 50 0 50 0 0 Bookbeat 55 25 30 0 0 Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0 Fricative Bookshop 60 35 0 0 25 Total 250 60 165 0 25*/
Can you create a cross-tab report in my SQL Server! 你能用sql server实现交叉表报表吗? How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993? 我如何能够得到所有仓库在1993年分季度的销售情况报表哪? You can use the table sales and stores in datatabase pubs. 以pubs数据库的销售表和仓库表为例: Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quality. Table stores record all store information. 销售表记录了所有仓库销售明细信息,列store_id为每个仓库的编号,列ord_date是每笔销售的日期,还有列qty是销售量。仓库信息表记录了所有的仓库信息。 I want to get the result look like as below: 我想得到如下结果: Output: 输出 stor_name Total Qtr1 Qtr2 Qtr3 Qtr4 ---------------------------------------- ----------- ----------- ----------- ----------- ----------- Barnum's 50 0 50 0 0 Bookbeat 55 25 30 0 0 Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0 Fricative Bookshop 60 35 0 0 25 Total 250 60 165 0 25how to 用动态SQL实现 the upper problem 如何用动态SQL实现这个问题。
declare @sql varchar(8000) select @sql=isnull(@sql+',','')+'sum(case when datepart(qq,date)='+ltrim(n)+' then qty else 0 end) as [qty'+cast(N as varchar(10))+']' from (select 1 as n union select 2 union all select 3 union select 4)a set @sql ='select model,sum(qty) as qty,'+@sql+' from test group by model' exec(@sql)
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)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 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
*/
set nocount on
create table test(model varchar(20),date datetime ,qty int)
insert into test select 'a','2001-2-01','10'
insert into test select 'a','2001-4-01','50'
insert into test select 'b','2001-5-01','100'
insert into test select 'b','2001-6-01','200'
insert into test select 'b','2001-7-01','100'
insert into test select 'c','2001-8-01','200'
insert into test select 'd','2001-9-01','300'
insert into test select 'e','2001-10-01','250'
insert into test select 'e','2001-11-01','100'
insert into test select 'f','2001-12-01','150'
go
--测试 declare @sql varchar(8000) select @sql=isnull(@sql+',','')+'sum(case when datepart(qq,date)='+ltrim(n)+' then qty else 0 end) as [qty'+cast(N as varchar(10))+']'
from (select 1 as n union select 2 union all select 3 union select 4)a set @sql ='select model,sum(qty) as qty,'+@sql+' from test group by model' exec(@sql) --删除测试环境
drop table test
set nocount off /*
model qty qty1 qty2 qty3 qty4
-------------------- ----------- ----------- ----------- ----------- -----------
a 60 10 50 0 0
b 400 0 300 100 0
c 200 0 0 200 0
d 300 0 0 300 0
e 350 0 0 0 350
f 150 0 0 0 150
*/
use pubsSELECT isnull(stor_name,'Total') as stor_name,sum(qty) as Total,
sum(case when datepart(qq,ord_date)=1 then qty else 0 end) as Qtr1,
sum(case when datepart(qq,ord_date)=2 then qty else 0 end) as Qtr2,
sum(case when datepart(qq,ord_date)=3 then qty else 0 end) as Qtr3,
sum(case when datepart(qq,ord_date)=4 then qty else 0 end) as Qtr4
FROM stores t JOIN
sales s on s.stor_id = t.stor_id
WHERE year(s.ord_date) = '1993'
group by stor_name with rollup
/*
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0
Fricative Bookshop 60 35 0 0 25
Total 250 60 165 0 25*/
你能用sql server实现交叉表报表吗?
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
我如何能够得到所有仓库在1993年分季度的销售情况报表哪?
You can use the table sales and stores in datatabase pubs.
以pubs数据库的销售表和仓库表为例:
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quality. Table stores record all store information.
销售表记录了所有仓库销售明细信息,列store_id为每个仓库的编号,列ord_date是每笔销售的日期,还有列qty是销售量。仓库信息表记录了所有的仓库信息。
I want to get the result look like as below:
我想得到如下结果:
Output:
输出
stor_name Total Qtr1 Qtr2 Qtr3 Qtr4
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's 50 0 50 0 0
Bookbeat 55 25 30 0 0
Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0
Fricative Bookshop 60 35 0 0 25
Total 250 60 165 0 25how to 用动态SQL实现 the upper problem 如何用动态SQL实现这个问题。
from (select 1 as n union select 2 union all select 3 union select 4)a set @sql ='select model,sum(qty) as qty,'+@sql+' from test group by model' exec(@sql)