把一个表里的数据同一天的数量汇总并横按日期横项显示问题
有一个表：
SELECT YG_NO,TRS_DD,QTY FROM TF_JBTZ ORDER BY YG_NO,trs_dd
比如有内容如下：
yg_no TRS_DD QTY
0002072007-03-01 00:00:00.000 4.00000000
0002072007-03-01 00:00:00.000 1.00000000
0002072007-03-02 00:00:00.000 3.00000000
0003682007-03-02 00:00:00.000 5.00000000
9912302007-03-01 00:00:00.000 6.00000000
9912302007-03-02 00:00:00.000 3.00000000
9912302007-03-07 00:00:00.000 3.00000000
9912302007-03-07 00:00:00.000 2.00000000
。。。。
要按日期TRS_DD查询并按yg_no排列汇总QTY的数值；
如上面000207在2007-03-01有一笔QTY为4，一笔QTY为1，
所以在2007-03-01汇总QTY的数值为5；
000207在2007-03-02只有一笔QTY为3，所以在
2007-03-02汇总QTY的数值为3；
000368在2007-03-02只有一笔QTY为5，所以在
2007-03-02汇总QTY的数值为5；
991230在2007-03-01只有一笔QTY为6，所以在
2007-03-02汇总QTY的数值为6；
991230在2007-03-02只有一笔QTY为3，所以在
2007-03-02汇总QTY的数值为3；
991230在2007-03-07有一笔QTY为3，一笔QTY为2，
所以在2007-03-07汇总QTY的数值为5；
比如依上面的数据按日期TRS_DD(2007-03-07~2007-03-31)查询得到如下格式内容：
YG_NO 3月1号 3月2号 3月3号 3月4号 3月5号 3月6号 3月7号...3月31号
000207 5 3 ...
000368 5 ...
991230 6 3 5 ...
......
请问应如何可以实现，谢谢哪位大虾来帮忙解答；
[解决办法]
--也算行轉列吧?沒有的日期也要顯示嗎?
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case when trs_dd= ' ' '+convert(char(10),trs_dd,120)+ ' ' ' then qty else 0 end) as [ '+convert(char(10),trs_dd,120)+ '] '
from T group by trs_dd
select @sql= 'select yg_no '+@sql+ ' from T group by yg_no '
exec (@sql)
/*
yg_no 2007-03-01 2007-03-02 2007-03-07
---------- ----------- ----------- -----------
000207 5 3 0
000368 0 5 0
991230 6 3 5
*/
要是沒有的日期也要顯示,建議create 一個臨時表,放3-1到3-31的日期,再關聯就ok
[解决办法]
-- 创建测试表
create table TF_JBTZ(YG_NO varchar(10),TRS_DD datetime,QTY int)
Go
-- 插入测试数据
insert TF_JBTZ select '000207 ', '2007-03-01 ',4
insert TF_JBTZ select '000207 ', '2007-03-01 ',1
insert TF_JBTZ select '000207 ', '2007-03-02 ',2
insert TF_JBTZ select '000368 ', '2007-03-02 ',5
insert TF_JBTZ select '991230 ', '2007-03-01 ',6
insert TF_JBTZ select '991230 ', '2007-03-02 ',3
insert TF_JBTZ select '991230 ', '2007-03-07 ',3
insert TF_JBTZ select '991230 ', '2007-03-07 ',4
Go
-- 创建存储过程
create proc dbo.PrcQueryData
@strStart char(10),
@strEnd char(10)
as
set nocount on
declare @dtStart datetime
declare @dtEnd datetime
declare @strSQL varchar(8000)
set @strSQL= ' '
set @dtStart=convert(datetime,@strStart,120)
set @dtEnd=convert(datetime,@strEnd,120)
while(@dtStart <@dtEnd)
begin
set @strSQL=@strSQL+ ',max(case convert(char(10),TRS_DD,120) when ' ' '+convert(char(10),@dtStart,120)+ ' ' ' then QTY end) as [ '+convert(char(10),@dtStart,120)+ '] '
set @dtStart=dateadd(d,1,@dtStart)
end
set @strSQL=stuff(@strSQL,1,1, ' ')
set @strSQL= 'select YG_NO, '+@strSQL+ ' from (select YG_NO,TRS_DD,sum(QTY) as QTY
from TF_JBTZ
group by YG_NO,TRS_DD) t
group by YG_NO '
exec(@strSQL)
Go
-- 执行存储过程
Exec dbo.PrcQueryData '2007-03-01 ', '2007-03-10 '
-- 结果
YG_NO 2007-03-01 2007-03-02 2007-03-03 2007-03-04 2007-03-05 2007-03-06 2007-03-07 2007-03-08 2007-03-09
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
000207 5 2 NULL NULL NULL NULL NULL NULL NULL
000368 NULL 5 NULL NULL NULL NULL NULL NULL NULL
991230 6 3 NULL NULL NULL NULL 7 NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
[解决办法]
declare @t table(yg_no varchar(10),trs_dd datetime,QTY float)
insert into @t select '000207 ', '2007-03-01 00:00:00.000 ',4
union all select '000207 ', '2007-03-01 ',1
union all select '000207 ', '2007-03-02 ',3
union all select '000368 ', '2007-03-02 ',5
union all select '991230 ', '2007-03-01 ',6
union all select '991230 ', '2007-03-02 ',3
union all select '991230 ', '2007-03-07 ',3
union all select '991230 ', '2007-03-07 ',2
select * from (select * from @t where datediff(day,trs_dd, '2007-03-31 ')> =0) t
pivot
(
sum(qty)
for trs_dd in(
[2007-03-01],[2007-03-02],[2007-03-03],[2007-03-04],[2007-03-05],
[2007-03-06],[2007-03-07]
)
)as pt
[解决办法]
--条件稍微修改下:
select * from (select * from @t where datediff(day, '2007-03-01 ',trs_dd)> =0 and datediff(day, '2007-03-31 ',trs_dd) <=0) t
pivot
(
sum(qty)
for trs_dd in(
[2007-03-01],[2007-03-02],[2007-03-03],[2007-03-04],[2007-03-05],
[2007-03-06],[2007-03-07]
)
)as pt
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
yg_no varchar(10),
TRS_DD datetime,
QTY int
)
insert into tb(yg_no,TRS_DD,QTY) values( '000207 ', '2007-03-01 ',4)
insert into tb(yg_no,TRS_DD,QTY) values( '000207 ', '2007-03-01 ',1)
insert into tb(yg_no,TRS_DD,QTY) values( '000207 ', '2007-03-02 ',3)
insert into tb(yg_no,TRS_DD,QTY) values( '000368 ', '2007-03-02 ',5)
insert into tb(yg_no,TRS_DD,QTY) values( '991230 ', '2007-03-01 ',6)
insert into tb(yg_no,TRS_DD,QTY) values( '991230 ', '2007-03-02 ',3)
insert into tb(yg_no,TRS_DD,QTY) values( '991230 ', '2007-03-07 ',3)
insert into tb(yg_no,TRS_DD,QTY) values( '991230 ', '2007-03-07 ',2)
select yg_no ,
sum(case when trs_dd = '2007-03-01 ' then qty else 0 end) as '3月1号 ',
sum(case when trs_dd = '2007-03-02 ' then qty else 0 end) as '3月2号 ',
sum(case when trs_dd = '2007-03-03 ' then qty else 0 end) as '3月3号 ',
sum(case when trs_dd = '2007-03-07 ' then qty else 0 end) as '3月7号 ',
sum(case when trs_dd = '2007-03-31 ' then qty else 0 end) as '3月31号 '
--其他日期自己加
from
(
select yg_no , convert(varchar(10),trs_dd,120) as trs_dd , sum(QTY) as QTY from tb group by yg_no , convert(varchar(10),trs_dd,120)
) t
group by yg_no
drop table tb
/*
yg_no 3月1号 3月2号 3月3号 3月7号 3月31号
---------- ----------- ----------- ----------- ----------- -----------
000207 5 3 0 0 0
000368 0 5 0 0 0
991230 6 3 0 5 0
（所影响的行数为 3 行）
*/
[解决办法]
以上为动态SQL的实现方式，如果楼主希望用一条SQL语句实现，那就需要确定到底有多少列，并一一在查询语句中列出来，如：
select
yg_no,
sum(case when trs_dd= '2007-03-01 ' then qty else 0 end) as [2007-03-01],
sum(case when trs_dd= '2007-03-02 ' then qty else 0 end) as [2007-03-02],
sum(case when trs_dd= '2007-03-07 ' then qty else 0 end) as [2007-03-07]
from
TF_JBTZ
group by
yg_no
