--也算行轉列吧?沒有的日期也要顯示嗎?
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
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 ondeclare @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)
endset @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.
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',2select * 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
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
drop table tb
gocreate 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_nodrop 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 行)
*/
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
上面是已确定了哪天的数据,但是在查询过程中是不知道哪天有什么数据的,
是通过开始与结束日来查出来才知道这一天是否有数据。
谢谢