根据价格修改记录，取每个月底的产品价格

[nickxzh]

有数据表TB1，如下图
产品 | 价格 | 修改日期
A | 10.00 | 2016.02.23
A | 9.80 | 2016.03.10
A | 11.05 | 2016.05.08
A | 11.00 | 2016.05.21
A | 9.50 | 2016.07.01
..................

然后根据当前日期，取当前日期以及前六个月每月月底的，A产品的价格。
要求结果：

产品 | 时间_16.08.25 | 时间_16.07.31 | 时间_16.06.30 | 时间_16.05.31 | 时间_16.04.30 | 时间_16.03.31 | 时间_16.02.28 |
A | 9.50 | 9.50 | 11.00 | 11.00 | 9.80 | 9.8 | 10.00 |

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#TB1') is null
	drop table #TB1
Go
Create table #TB1([产品] nvarchar(21),[价格] decimal(18,2),[修改日期] DATE)
Insert #TB1
select N'A',10.00,'2016.02.23' union all
select N'A',9.80,'2016.03.10' union all
select N'A',11.05,'2016.05.08' union all
select N'A',11.00,'2016.05.21' union all
select N'A',9.50,'2016.07.01'
GO

DECLARE @Sql NVARCHAR(max)=''


;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+'01') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
)
Select @Sql=@Sql+',[时间_'+CONVERT(VARCHAR(8),DT,2)+']=max(case when DT='''+CONVERT(VARCHAR(8),DT,2)+''' then [价格] end)' from CTEDT;

EXEC('
;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+''01'') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
),CTEDT2
AS
(
SELECT CONVERT(VARCHAR(8),DT,2) AS DT,b.价格 FROM CTEDT AS a CROSS APPLY(SELECT TOP 1 [价格] FROM #TB1 WHERE [产品]=N''A'' AND [修改日期]<=a.DT ORDER BY [修改日期] DESC) AS b
)
SELECT [产品]=N''A'''+@Sql+' FROM CTEDT2')

/*
产品	时间_16.08.25	时间_16.07.31	时间_16.06.30	时间_16.05.31	时间_16.04.30	时间_16.03.31	时间_16.02.29
A	9.50	9.50	11.00	11.00	9.80	9.80	10.00*/

[中国风]

查所有产品时这样用

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#TB1') is null
	drop table #TB1
Go
Create table #TB1([产品] nvarchar(21),[价格] decimal(18,2),[修改日期] DATE)
Insert #TB1
select N'A',10.00,'2016.02.23' union all
select N'A',9.80,'2016.03.10' union all
select N'A',11.05,'2016.05.08' union all
select N'A',11.00,'2016.05.21' union all
select N'A',9.50,'2016.07.01'
GO

DECLARE @Sql NVARCHAR(max)=''


;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+'01') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
)
Select @Sql=@Sql+',[时间_'+CONVERT(VARCHAR(8),DT,2)+']=max(case when DT='''+CONVERT(VARCHAR(8),DT,2)+''' then [价格] end)' from CTEDT;

EXEC('
;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+''01'') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
),CTEDT1
AS
(
SELECT * FROM CTEDT,(SELECT DISTINCT 产品 FROM #TB1) AS B 
),CTEDT2
AS
(
SELECT A.产品,CONVERT(VARCHAR(8),DT,2) AS DT,b.价格 FROM CTEDT1 AS a CROSS APPLY(SELECT TOP 1 [价格] FROM #TB1 WHERE [产品]=a.[产品] AND [修改日期]<=a.DT ORDER BY [修改日期] DESC) AS b
)
SELECT [产品]'+@Sql+' FROM CTEDT2 GROUP BY 产品')

/*
产品	时间_16.08.25	时间_16.07.31	时间_16.06.30	时间_16.05.31	时间_16.04.30	时间_16.03.31	时间_16.02.29
A	9.50	9.50	11.00	11.00	9.80	9.80	10.00*/

[nickxzh]

引用 2 楼 roy_88 的回复:

查所有产品时这样用

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#TB1') is null
	drop table #TB1
Go
Create table #TB1([产品] nvarchar(21),[价格] decimal(18,2),[修改日期] DATE)
Insert #TB1
select N'A',10.00,'2016.02.23' union all
select N'A',9.80,'2016.03.10' union all
select N'A',11.05,'2016.05.08' union all
select N'A',11.00,'2016.05.21' union all
select N'A',9.50,'2016.07.01'
GO

DECLARE @Sql NVARCHAR(max)=''


;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+'01') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
)
Select @Sql=@Sql+',[时间_'+CONVERT(VARCHAR(8),DT,2)+']=max(case when DT='''+CONVERT(VARCHAR(8),DT,2)+''' then [价格] end)' from CTEDT;

EXEC('
;WITH CTEDT
AS
(
SELECT GETDATE() AS DT 
UNION ALL
SELECT DATEADD(dd,-1,CONVERT(VARCHAR(8),Dt,102)+''01'') FROM CTEDT WHERE DATEDIFF(mm,DT,GETDATE())<6
),CTEDT1
AS
(
SELECT * FROM CTEDT,(SELECT DISTINCT 产品 FROM #TB1) AS B 
),CTEDT2
AS
(
SELECT A.产品,CONVERT(VARCHAR(8),DT,2) AS DT,b.价格 FROM CTEDT1 AS a CROSS APPLY(SELECT TOP 1 [价格] FROM #TB1 WHERE [产品]=a.[产品] AND [修改日期]<=a.DT ORDER BY [修改日期] DESC) AS b
)
SELECT [产品]'+@Sql+' FROM CTEDT2 GROUP BY 产品')

/*
产品	时间_16.08.25	时间_16.07.31	时间_16.06.30	时间_16.05.31	时间_16.04.30	时间_16.03.31	时间_16.02.29
A	9.50	9.50	11.00	11.00	9.80	9.80	10.00*/

Roy_88 大神，这个EXEC后的结果有没有办法保存到一个表里面，因为这个结果后面还会要用到的？

[中国风]

可以生成新表，你的列是动态可用INTO SELECT [产品]'+@Sql+' INTO 新表 FROM CTEDT2 GROUP BY 产品'