取上个月价格信息,即12月份每天的价格信息样本数据:
declare @a table(ProductID int,Price decimal(8,2),PriceDate datetime)
insert @a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
declare @b table(ProductID int,ProductName varchar(20))
insert @b select 1,'Product1' union all
select 2,'Product2'
ProductID Price PriceDate
----------- --------------------------------------- -----------------------
1 1.20 2007-12-01 00:00:00.000
1 1.50 2007-12-02 00:00:00.000
2 1.50 2007-12-01 00:00:00.000
2 1.80 2007-12-02 00:00:00.000
ProductID ProductName
----------- --------------------
1 Product1
2 Product2期望结果()
ProductID ProductName 2007-12-01 2007-12-02 2007-12-03 … 2007-12-31
1 Product1 1.20 1.50 1.50 1.50
2 Product2 1.50 1.80 1.80 1.80若当天没有数据,就使用前一天的数据,月份列最好能动态生成。
declare @a table(ProductID int,Price decimal(8,2),PriceDate datetime)
insert @a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
declare @b table(ProductID int,ProductName varchar(20))
insert @b select 1,'Product1' union all
select 2,'Product2'
ProductID Price PriceDate
----------- --------------------------------------- -----------------------
1 1.20 2007-12-01 00:00:00.000
1 1.50 2007-12-02 00:00:00.000
2 1.50 2007-12-01 00:00:00.000
2 1.80 2007-12-02 00:00:00.000
ProductID ProductName
----------- --------------------
1 Product1
2 Product2期望结果()
ProductID ProductName 2007-12-01 2007-12-02 2007-12-03 … 2007-12-31
1 Product1 1.20 1.50 1.50 1.50
2 Product2 1.50 1.80 1.80 1.80若当天没有数据,就使用前一天的数据,月份列最好能动态生成。
解决方案 »
- 求助SQL语句
- charindex如何处理的?
- 散分求一句SQL语句,各位高手来帮帮忙!!!小弟感激不禁!!!(在线等)
- SQL 转化txt出问题 -------一定给分
- 一个带分支(IF)的SQL语句怎么写?
- 如何查询数据库中指定表的所占的磁盘空间
- 将sql数据备份导入的sqlsever后,数据库连接不上?
- 求SQL语句
- 当前事务未能导出到远程提供程序。已回滚了该事务。急!!!
- 关于sqlserver加密的问题
- SQLSever2005的Analysis Service中,measure选择AggregateFunction=NONE时,怎么都查询不出结果.
- 有关在sql2005导出脚本的问题
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下:即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
declare @a table(ProductID int,Price decimal(8,2),PriceDate datetime)
insert @a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
declare @b table(ProductID int,ProductName varchar(20))
insert @b select 1,'Product1' union all
select 2,'Product2'
declare @d table (
Days datetime
)insert @d
select dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')
from (select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9 union all select 10 union all select 11
union all select 12 union all select 13 union all select 14 union all select 15
union all select 16 union all select 17 union all select 18 union all select 19
union all select 20 union all select 21 union all select 22 union all select 23
union all select 24 union all select 25 union all select 26 union all select 27
union all select 28 union all select 29 union all select 30 union all select 31
) as t
where dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')<dateadd(month,1,convert(varchar(7),getdate(),120)+'-01')select b.*,d.*,a.Price
from @b b cross join @d d
left join @a a
on a.ProductID=b.ProductID
and a.PriceDate=(select max(PriceDate) from @a where ProductID=b.ProductID and PriceDate<=d.Days)
--表变量不好玩,改成临时表
create table #a(ProductID int,Price decimal(8,2),PriceDate datetime) insert #a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
create table #b(ProductID int,ProductName varchar(20))
insert #b select 1,'Product1' union all
select 2,'Product2'
Create table #d (
Days datetime
)insert #d
select dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')
from (select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9 union all select 10 union all select 11
union all select 12 union all select 13 union all select 14 union all select 15
union all select 16 union all select 17 union all select 18 union all select 19
union all select 20 union all select 21 union all select 22 union all select 23
union all select 24 union all select 25 union all select 26 union all select 27
union all select 28 union all select 29 union all select 30 union all select 31
) as t
where dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')<dateadd(month,1,convert(varchar(7),getdate(),120)+'-01')declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case d.Days when '''+convert(varchar(10),days,120)+''' then a.Price end) as ['+convert(varchar(10),days,120)+']'
from #d
order by daysexec('
select b.*'+@sql+'
from #b b cross join #d d
left join #a a
on a.ProductID=b.ProductID
and a.PriceDate=(select max(PriceDate) from #a where ProductID=b.ProductID and PriceDate<=d.Days)
group by b.ProductID,b.ProductName
')drop table #a,#b,#d--结果
ProductID ProductName 2008-01-01 2008-01-02 2008-01-03 2008-01-04 2008-01-05 2008-01-06 2008-01-07 2008-01-08 2008-01-09 2008-01-10 2008-01-11 2008-01-12 2008-01-13 2008-01-14 2008-01-15 2008-01-16 2008-01-17 2008-01-18 2008-01-19 2008-01-20 2008-01-21 2008-01-22 2008-01-23 2008-01-24 2008-01-25 2008-01-26 2008-01-27 2008-01-28 2008-01-29 2008-01-30 2008-01-31
----------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 Product1 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50
2 Product2 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80警告: 聚合或其它 SET 操作消除了空值。
--表变量不好玩,改成临时表
create table #a(ProductID int,Price decimal(8,2),PriceDate datetime) insert #a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
create table #b(ProductID int,ProductName varchar(20))
insert #b select 1,'Product1' union all
select 2,'Product2'
Create table #d (
Days datetime
)insert #d
select dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')
from (select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9 union all select 10 union all select 11
union all select 12 union all select 13 union all select 14 union all select 15
union all select 16 union all select 17 union all select 18 union all select 19
union all select 20 union all select 21 union all select 22 union all select 23
union all select 24 union all select 25 union all select 26 union all select 27
union all select 28 union all select 29 union all select 30 union all select 31
) as t
where dateadd(day,i,convert(varchar(7),getdate(),120)+'-01')<dateadd(month,1,convert(varchar(7),getdate(),120)+'-01')declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case d.Days when '''+convert(varchar(10),days,120)+''' then a.Price end) as ['+convert(varchar(10),days,120)+']'
from #d
order by daysexec('
select b.*'+@sql+'
from #b b cross join #d d
left join #a a
on a.ProductID=b.ProductID
and a.PriceDate=(select max(PriceDate) from #a where ProductID=b.ProductID and PriceDate<=dateadd(month,-1,d.Days))
group by b.ProductID,b.ProductName
')drop table #a,#b,#d--结果
ProductID ProductName 2008-01-01 2008-01-02 2008-01-03 2008-01-04 2008-01-05 2008-01-06 2008-01-07 2008-01-08 2008-01-09 2008-01-10 2008-01-11 2008-01-12 2008-01-13 2008-01-14 2008-01-15 2008-01-16 2008-01-17 2008-01-18 2008-01-19 2008-01-20 2008-01-21 2008-01-22 2008-01-23 2008-01-24 2008-01-25 2008-01-26 2008-01-27 2008-01-28 2008-01-29 2008-01-30 2008-01-31
----------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 Product1 1.20 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50
2 Product2 1.50 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80警告: 聚合或其它 SET 操作消除了空值。
create table A(ProductID int,Price decimal(8,2),PriceDate datetime)
insert A select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
create table B(ProductID int,ProductName varchar(20))
insert B select 1,'Product1' union all
select 2,'Product2' select t1.* , isnull(t2.Price , (select top 1 price from A where ProductID = t1.ProductID and PriceDate < t1.rq order by pricedate desc)) price from
(
select distinct m.productid , n.rq from
(select productid , PriceDate from A) m
cross join
(select rq = dateadd(day , t.id , convert(varchar(7),dateadd(mm , -1 , getdate()) ,120) + '-01') from
(
select 0 as id
union select 1 union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
) t
) n
where convert(varchar(7),n.rq,120) = convert(varchar(7),dateadd(mm , -1 , getdate()) ,120)
) t1 left join A t2 on t1.productid = t2.productid and t1.rq = t2.PriceDate
order by t1.ProductID , t1.rqdrop table A,B/*
productid rq price
----------- ------------------------------------------------------ ----------
1 2007-12-01 00:00:00.000 1.20
1 2007-12-02 00:00:00.000 1.50
1 2007-12-03 00:00:00.000 1.50
1 2007-12-04 00:00:00.000 1.50
1 2007-12-05 00:00:00.000 1.50
1 2007-12-06 00:00:00.000 1.50
1 2007-12-07 00:00:00.000 1.50
1 2007-12-08 00:00:00.000 1.50
1 2007-12-09 00:00:00.000 1.50
1 2007-12-10 00:00:00.000 1.50
1 2007-12-11 00:00:00.000 1.50
1 2007-12-12 00:00:00.000 1.50
1 2007-12-13 00:00:00.000 1.50
1 2007-12-14 00:00:00.000 1.50
1 2007-12-15 00:00:00.000 1.50
1 2007-12-16 00:00:00.000 1.50
1 2007-12-17 00:00:00.000 1.50
1 2007-12-18 00:00:00.000 1.50
1 2007-12-19 00:00:00.000 1.50
1 2007-12-20 00:00:00.000 1.50
1 2007-12-21 00:00:00.000 1.50
1 2007-12-22 00:00:00.000 1.50
1 2007-12-23 00:00:00.000 1.50
1 2007-12-24 00:00:00.000 1.50
1 2007-12-25 00:00:00.000 1.50
1 2007-12-26 00:00:00.000 1.50
1 2007-12-27 00:00:00.000 1.50
1 2007-12-28 00:00:00.000 1.50
1 2007-12-29 00:00:00.000 1.50
1 2007-12-30 00:00:00.000 1.50
1 2007-12-31 00:00:00.000 1.50
2 2007-12-01 00:00:00.000 1.50
2 2007-12-02 00:00:00.000 1.80
2 2007-12-03 00:00:00.000 1.80
2 2007-12-04 00:00:00.000 1.80
2 2007-12-05 00:00:00.000 1.80
2 2007-12-06 00:00:00.000 1.80
2 2007-12-07 00:00:00.000 1.80
2 2007-12-08 00:00:00.000 1.80
2 2007-12-09 00:00:00.000 1.80
2 2007-12-10 00:00:00.000 1.80
2 2007-12-11 00:00:00.000 1.80
2 2007-12-12 00:00:00.000 1.80
2 2007-12-13 00:00:00.000 1.80
2 2007-12-14 00:00:00.000 1.80
2 2007-12-15 00:00:00.000 1.80
2 2007-12-16 00:00:00.000 1.80
2 2007-12-17 00:00:00.000 1.80
2 2007-12-18 00:00:00.000 1.80
2 2007-12-19 00:00:00.000 1.80
2 2007-12-20 00:00:00.000 1.80
2 2007-12-21 00:00:00.000 1.80
2 2007-12-22 00:00:00.000 1.80
2 2007-12-23 00:00:00.000 1.80
2 2007-12-24 00:00:00.000 1.80
2 2007-12-25 00:00:00.000 1.80
2 2007-12-26 00:00:00.000 1.80
2 2007-12-27 00:00:00.000 1.80
2 2007-12-28 00:00:00.000 1.80
2 2007-12-29 00:00:00.000 1.80
2 2007-12-30 00:00:00.000 1.80
2 2007-12-31 00:00:00.000 1.80(所影响的行数为 62 行)
*/
你可以把getdate()替换成一个参数
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
create table #b(ProductID int,ProductName varchar(20))
insert #b select 1,'Product1' union all
select 2,'Product2'
Create table #d (
Days datetime
)insert #d
select dateadd(day,i,convert(varchar(7),dateadd(month,-1,getdate()),120)+'-01')
from (select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9 union all select 10 union all select 11
union all select 12 union all select 13 union all select 14 union all select 15
union all select 16 union all select 17 union all select 18 union all select 19
union all select 20 union all select 21 union all select 22 union all select 23
union all select 24 union all select 25 union all select 26 union all select 27
union all select 28 union all select 29 union all select 30 union all select 31
) as t
where dateadd(day,i,convert(varchar(7),dateadd(month,-1,getdate()),120)+'-01')<convert(varchar(7),getdate(),120)+'-01'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case d.Days when '''+convert(varchar(10),days,120)+''' then a.Price end) as ['+convert(varchar(10),days,120)+']'
from #d
order by daysexec('
select b.*'+@sql+'
from #b b cross join #d d
left join #a a
on a.ProductID=b.ProductID
and a.PriceDate=(select max(PriceDate) from #a where ProductID=b.ProductID and PriceDate<=d.Days)
group by b.ProductID,b.ProductName
')drop table #a,#b,#d--结果
ProductID ProductName 2007-12-01 2007-12-02 2007-12-03 2007-12-04 2007-12-05 2007-12-06 2007-12-07 2007-12-08 2007-12-09 2007-12-10 2007-12-11 2007-12-12 2007-12-13 2007-12-14 2007-12-15 2007-12-16 2007-12-17 2007-12-18 2007-12-19 2007-12-20 2007-12-21 2007-12-22 2007-12-23 2007-12-24 2007-12-25 2007-12-26 2007-12-27 2007-12-28 2007-12-29 2007-12-30 2007-12-31
----------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 Product1 1.20 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50
2 Product2 1.50 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80警告: 聚合或其它 SET 操作消除了空值。
insert @a
select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02' union all
select 1,1.9,'2007-12-03' union all
select 2,1.4,'2007-12-03'declare @sql varchar(8000);set @sql = 'declare @a table(ProductID int,Price decimal(8,2),PriceDate datetime)
insert @a
select 1,1.20,''2007-12-01'' union all
select 1,1.50,''2007-12-02'' union all
select 2,1.50,''2007-12-01'' union all
select 2,1.80,''2007-12-02'' union all
select 1,1.9,''2007-12-03'' union all
select 2,1.4,''2007-12-03''declare @b table(ProductID int,ProductName varchar(20))
insert @b
select 1,''Product1'' union all
select 2,''Product2'' 'set @sql = @sql + 'select p2.ProductID,p2.ProductName';select @sql = @sql + ',[' + cast(year(PriceDate) as varchar) + '-' + cast(month(PriceDate) as varchar) + '-' + cast(day(PriceDate) as varchar) + '] = max(case when cast(PriceDate as varchar)='''+ cast(PriceDate as varchar) + ''' then price else null end )'
from (select distinct(PriceDate) from @a) paexec(@sql + ' from @a p1 inner join @b p2 on p1.ProductID=p2.ProductID group by p2.ProductID ,p2.ProductName')Print @sql + ' from @a p1 inner join @b p2 on p1.ProductID=p2.ProductID group by p2.ProductID,p2.ProductName';
set nocount on
if object_id('tempdb..#a') is not null drop table #a
create table #a (ProductID int,Price decimal(8,2),PriceDate datetime)
insert #a select 1,1.20,'2007-12-01' union all
select 1,1.50,'2007-12-02' union all
select 2,1.50,'2007-12-01' union all
select 2,1.80,'2007-12-02'
if object_id('tempdb..#b') is not null drop table #b
create table #b (ProductID int,ProductName varchar(20))
insert #b select 1,'Product1' union all
select 2,'Product2' declare @curyear int,@curmonth int,@date1 datetime,@date2 datetime,@lstr1 varchar(8000)
select @curyear=2008,@curmonth=1
set @date1=convert(datetime,convert(varchar(4),@curyear)+'-'+convert(varchar(2),@curmonth)+'-1',120)
set @date2=dateadd(dd,-1,@date1)
set @date1=dateadd(mm,-1,@date1)
set @lstr1='select productid,productname'
while @date1<=@date2
begin
set @lstr1=@lstr1+',['+convert(varchar(10),@date1,120)
+']=(select top 1 price from #a where productid=#b.productid and pricedate<='''+convert(varchar(10),@date1,120)+''' order by pricedate desc)'
set @date1=dateadd(dd,1,@date1)
end
set @lstr1=@lstr1+' from #b order by productid'
exec(@lstr1)
CREATE TABLE #A(PRODUCTID INT,PRICE DECIMAL(8,2),PRICEDATE DATETIME) INSERT #A SELECT 1,1.20,'2007-12-01'
UNION ALL SELECT 1,1.50,'2007-12-02'
UNION ALL SELECT 2,1.50,'2007-12-01'
UNION ALL SELECT 2,1.80,'2007-12-02'
UNION ALL SELECT 2,1.90,'2007-12-04'
UNION ALL SELECT 1,1.60,'2007-12-05' CREATE TABLE #B(PRODUCTID INT,PRODUCTNAME VARCHAR(20))
INSERT #B SELECT 1,'PRODUCT1'
UNION ALL SELECT 2,'PRODUCT2' DECLARE @SQL VARCHAR(8000) SET @SQL = ''
DECLARE @DAYS DATETIME SET @DAYS = '2007-12-1'
WHILE @DAYS < '2008-1-1' BEGIN
SET @SQL = @SQL +
',(SELECT TOP 1 PRICE FROM #A WHERE (PRODUCTID = B.PRODUCTID) AND (PRICEDATE <= '''
+ CONVERT(VARCHAR(10),@DAYS,120)
+ ''') ORDER BY PRICEDATE DESC) AS ['
+ CONVERT(VARCHAR(10),@DAYS,120) + ']'
SET @DAYS = @DAYS + 1;
ENDSET @SQL = 'SELECT B.* ' + @SQL + ' FROM #B AS B'
EXEC(@SQL)
DROP TABLE #A,#B