有一个表,结构如下:
P_DATE [日期] :
T1 [零售量(吨)]
T2 [吨油毛利(元/吨)]
T3 [其中:93#]
T4 [0#]
T5 [吨油费用(元/吨)]
T6 [吨油利润(元/吨)]
T7 [利润总额(万元)]
注:(中括号里汉字代表下面列的中文名字)
写一个存储过程 根据传入的日期(2011-08-09)往前推8天 查询这个表,展示效果是这样
项目 7月31日 8月1日 8月2日 ..........8月9日
T1[零售量(吨)] 77.8 . 88.4
T2[吨油毛利(元/吨)] 67.9 . .
T3[其中:93#] 89.5 . .
T4[0#] 88.7 . .
T5[吨油费用(元/吨)] 67.4 . .
T6[吨油利润(元/吨)] 93.2 . .
T7[利润总额(万元)] 76.5 . 66.9
P_DATE [日期] :
T1 [零售量(吨)]
T2 [吨油毛利(元/吨)]
T3 [其中:93#]
T4 [0#]
T5 [吨油费用(元/吨)]
T6 [吨油利润(元/吨)]
T7 [利润总额(万元)]
注:(中括号里汉字代表下面列的中文名字)
写一个存储过程 根据传入的日期(2011-08-09)往前推8天 查询这个表,展示效果是这样
项目 7月31日 8月1日 8月2日 ..........8月9日
T1[零售量(吨)] 77.8 . 88.4
T2[吨油毛利(元/吨)] 67.9 . .
T3[其中:93#] 89.5 . .
T4[0#] 88.7 . .
T5[吨油费用(元/吨)] 67.4 . .
T6[吨油利润(元/吨)] 93.2 . .
T7[利润总额(万元)] 76.5 . 66.9
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
(
@time datetime
)
as
select
项目,
max(case when P_DATE=dateadd(dd,-1,@time) then 资金 else 0 end) ,
max(case when P_DATE=dateadd(dd,-2,@time) then 资金 else 0 end) ,.....
from
tb
group by
项目
insert into tb select '2011-7-30',100,200,30,70,160,40,4000
insert into tb select '2011-7-31',110,200,30,70,160,40,4400
insert into tb select '2011-8-1',120,200,30,70,160,40,4800
insert into tb select '2011-8-2',130,200,30,70,160,40,5200
insert into tb select '2011-8-3',100,200,30,70,160,40,4000
insert into tb select '2011-8-4',160,200,30,70,160,40,6400
insert into tb select '2011-8-5',110,200,30,70,160,40,4400
insert into tb select '2011-8-6',140,200,30,70,160,40,5600
insert into tb select '2011-8-7',100,200,30,70,160,40,4000
insert into tb select '2011-8-8',140,200,30,70,160,40,4800
insert into tb select '2011-8-9',100,200,30,70,160,40,4000
insert into tb select '2011-8-10',180,200,30,70,160,40,7200
go
declare @s nvarchar(max)select * into ##t from(
select convert(varchar(5),p_date,110) as dt,'t1' as 项目,t1 as v from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t2',t2 from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t3',t3 from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t4',t4 from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t5',t5 from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t6',t6 from tb where p_date between '2011-07-31' and '2011-09-09'
union all
select convert(varchar(5),p_date,110) as dt,'t7',t7 from tb where p_date between '2011-07-31' and '2011-09-09'
)t
select @s=isnull(@s+',','')+'['+ dt +']' from(
select distinct [dt] from ##t
)t
exec('select [项目],'+@s+' from ##t pivot (max([v]) for dt in('+@s+'))b')
/*
项目 07-31 08-01 08-02 08-03 08-04 08-05 08-06 08-07 08-08 08-09 08-10
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
t1 110.00 120.00 130.00 100.00 160.00 110.00 140.00 100.00 140.00 100.00 180.00
t2 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00
t3 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
t4 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00
t5 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00
t6 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00
t7 4400.00 4800.00 5200.00 4000.00 6400.00 4400.00 5600.00 4000.00 4800.00 4000.00 7200.00(7 行受影响)*/
go
drop table tb,##t
select distinct [dt] from ##t这两行报错
declare @sql as varchar(1000)
set @dt = '2011-08-09' --这里严格按照四位年,两位月,两位日输入。
set @sql = 'select 项目 ,
sum(case when datediff(dd,p_date,''' + @dt + ''') = 8 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-8,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 7 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-7,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 6 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-6,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 5 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-5,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 4 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-4,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 3 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-3,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 2 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-2,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 1 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-1,'''+@dt+'''),120) ]
from tb
group by 项目'exec(@sql)
declare @dt as varchar(10)
declare @sql as varchar(1000)
set @dt = '2011-08-09' --这里严格按照四位年,两位月,两位日输入。
set @sql = 'select 项目 ,
sum(case when datediff(dd,p_date,''' + @dt + ''') = 8 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-8,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 7 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-7,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 6 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-6,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 5 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-5,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 4 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-4,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 3 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-3,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 2 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-2,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 1 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-1,'''+@dt+'''),120) ]
from tb
group by 项目'exec(@sql)--从前推8天,含当天
declare @dt as varchar(10)
declare @sql as varchar(1000)
set @dt = '2011-08-09' --这里严格按照四位年,两位月,两位日输入。
set @sql = 'select 项目 ,
sum(case when datediff(dd,p_date,''' + @dt + ''') = 7 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-7,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 6 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-6,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 5 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-5,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 4 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-4,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 3 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-3,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 2 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-2,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 1 then 用于计算的某列 else 0 end) [ convert(varchar(10),dateadd(dd,-1,'''+@dt+'''),120) ],
sum(case when datediff(dd,p_date,''' + @dt + ''') = 0 then 用于计算的某列 else 0 end) [ '''+@dt+''']
from tb
group by 项目'exec(@sql)
select distinct [dt] from ##t
我是sql08 这两行是报错的,报dt无效,和对象名'##t'无效
create table tb(p_date datetime,t1 decimal(10,2),t2 decimal(10,2),t3 decimal(10,2),t4 decimal(10,2),t5 decimal(10,2),t6 decimal(10,2),t7 decimal(10,2))
insert into tb select '2011-7-30',100,200,30,70,160,40,4000
insert into tb select '2011-7-31',110,200,30,70,160,40,4400
insert into tb select '2011-8-1',120,200,30,70,160,40,4800
insert into tb select '2011-8-2',130,200,30,70,160,40,5200
insert into tb select '2011-8-3',100,200,30,70,160,40,4000
insert into tb select '2011-8-4',160,200,30,70,160,40,6400
insert into tb select '2011-8-5',110,200,30,70,160,40,4400
insert into tb select '2011-8-6',140,200,30,70,160,40,5600
insert into tb select '2011-8-7',100,200,30,70,160,40,4000
insert into tb select '2011-8-8',140,200,30,70,160,40,4800
insert into tb select '2011-8-9',100,200,30,70,160,40,4000
insert into tb select '2011-8-10',180,200,30,70,160,40,7200查询结果
/*
项目 07-31 08-01 08-02 08-03 08-04 08-05 08-06 08-07 08-08 08-09 08-10
---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
t1 110.00 120.00 130.00 100.00 160.00 110.00 140.00 100.00 140.00 100.00 180.00
t2 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00
t3 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
t4 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00
t5 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00
t6 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00
t7 4400.00 4800.00 5200.00 4000.00 6400.00 4400.00 5600.00 4000.00 4800.00 4000.00 7200.00
create table tb(p_date datetime,t1 decimal(10,2),t2 decimal(10,2),t3 decimal(10,2),t4 decimal(10,2),t5 decimal(10,2),t6 decimal(10,2),t7 decimal(10,2))
insert into tb select '2011-7-30',100,200,30,70,160,40,4000
insert into tb select '2011-7-31',110,200,30,70,160,40,4400
insert into tb select '2011-8-1',120,200,30,70,160,40,4800
insert into tb select '2011-8-2',130,200,30,70,160,40,5200
insert into tb select '2011-8-3',100,200,30,70,160,40,4000
insert into tb select '2011-8-4',160,200,30,70,160,40,6400
insert into tb select '2011-8-5',110,200,30,70,160,40,4400
insert into tb select '2011-8-6',140,200,30,70,160,40,5600
insert into tb select '2011-8-7',100,200,30,70,160,40,4000
insert into tb select '2011-8-8',140,200,30,70,160,40,4800
insert into tb select '2011-8-9',100,200,30,70,160,40,4000
insert into tb select '2011-8-10',180,200,30,70,160,40,7200
godeclare @dt as datetime
declare @sql as varchar(8000)set @dt = '2011-08-09'
set @sql = 'select ''t1'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t1 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t2'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t2 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t3'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t3 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t4'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t4 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t5'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t5 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t6'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t6 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t7'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t7 else 0 end) [' + convert(varchar(10),p_date,120) + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'exec(@sql)
drop table tb/*
项目 2011-08-01 2011-08-02 2011-08-03 2011-08-04 2011-08-05 2011-08-06 2011-08-07 2011-08-08 2011-08-09
---- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
t1 120.00 130.00 100.00 160.00 110.00 140.00 100.00 140.00 100.00
t2 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00
t3 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
t4 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00
t5 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00
t6 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00
t7 4800.00 5200.00 4000.00 6400.00 4400.00 5600.00 4000.00 4800.00 4000.00
*/
create table tb(p_date datetime,t1 decimal(10,2),t2 decimal(10,2),t3 decimal(10,2),t4 decimal(10,2),t5 decimal(10,2),t6 decimal(10,2),t7 decimal(10,2))
insert into tb select '2011-7-30',100,200,30,70,160,40,4000
insert into tb select '2011-7-31',110,200,30,70,160,40,4400
insert into tb select '2011-8-1',120,200,30,70,160,40,4800
insert into tb select '2011-8-2',130,200,30,70,160,40,5200
insert into tb select '2011-8-3',100,200,30,70,160,40,4000
insert into tb select '2011-8-4',160,200,30,70,160,40,6400
insert into tb select '2011-8-5',110,200,30,70,160,40,4400
insert into tb select '2011-8-6',140,200,30,70,160,40,5600
insert into tb select '2011-8-7',100,200,30,70,160,40,4000
insert into tb select '2011-8-8',140,200,30,70,160,40,4800
insert into tb select '2011-8-9',100,200,30,70,160,40,4000
insert into tb select '2011-8-10',180,200,30,70,160,40,7200
godeclare @dt as datetime
declare @sql as varchar(8000)set @dt = '2011-08-09'
set @sql = 'select ''t1'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t1 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t2'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t2 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t3'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t3 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t4'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t4 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t5'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t5 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t6'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t6 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t7'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t7 else 0 end) [' + replace(substring(convert(varchar(10),p_date,120),6,5),'-','月') + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'exec(@sql)
drop table tb/*
项目 08月01日 08月02日 08月03日 08月04日 08月05日 08月06日 08月07日 08月08日 08月09日
---- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
t1 120.00 130.00 100.00 160.00 110.00 140.00 100.00 140.00 100.00
t2 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00
t3 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
t4 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00
t5 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00
t6 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00
t7 4800.00 5200.00 4000.00 6400.00 4400.00 5600.00 4000.00 4800.00 4000.00*/
insert into tb select '2011-7-30',100,200,30,70,160,40,4000
insert into tb select '2011-7-31',110,200,30,70,160,40,4400
insert into tb select '2011-8-1',120,200,30,70,160,40,4800
insert into tb select '2011-8-2',130,200,30,70,160,40,5200
insert into tb select '2011-8-3',100,200,30,70,160,40,4000
insert into tb select '2011-8-4',160,200,30,70,160,40,6400
insert into tb select '2011-8-5',110,200,30,70,160,40,4400
insert into tb select '2011-8-6',140,200,30,70,160,40,5600
insert into tb select '2011-8-7',100,200,30,70,160,40,4000
insert into tb select '2011-8-8',140,200,30,70,160,40,4800
insert into tb select '2011-8-9',100,200,30,70,160,40,4000
insert into tb select '2011-8-10',180,200,30,70,160,40,7200
godeclare @dt as datetime
declare @sql as varchar(8000)set @dt = '2011-08-09'
set @sql = 'select ''t1'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t1 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t2'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t2 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t3'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t3 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t4'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t4 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t5'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t5 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t6'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t6 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'
set @sql = @sql + ' union all select ''t7'' 项目 '
select @sql = @sql + ' , max(case convert(varchar(10),p_date,120) when ''' + convert(varchar(10),p_date,120) + ''' then t7 else 0 end) [' + cast(datepart(mm,p_date) as varchar) + '月' + cast(datepart(day,p_date) as varchar) + '日' + ']'
from (select distinct p_date from tb where p_date between dateadd(dd,-8,@dt) and @dt) as a
set @sql = @sql + ' from tb'exec(@sql)
drop table tb/*
项目 8月1日 8月2日 8月3日 8月4日 8月5日 8月6日 8月7日 8月8日 8月9日
---- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
t1 120.00 130.00 100.00 160.00 110.00 140.00 100.00 140.00 100.00
t2 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00 200.00
t3 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00 30.00
t4 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00 70.00
t5 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00 160.00
t6 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00 40.00
t7 4800.00 5200.00 4000.00 6400.00 4400.00 5600.00 4000.00 4800.00 4000.00
*/