您可以使用“动态旋转”解决问题。请看这篇文章
试试这个
DECLARE @t TABLE(Id_Contract INT, Dt DATETIME,Amount INT)INSERT INTO @t SELECt 1,'2012-01-01 00:00:00.000',500INSERT INTO @t SELECT 1,'2012-03-01 00:00:00.000',450INSERT INTO @t SELECT 2,'2012-09-01 00:00:00.000',300INSERT INTO @t SELECT 3,'2012-08-01 00:00:00.000',750DECLARE @cols AS VARCHAr(MAX), @query AS VARCHAr(MAX);SELECT Id_Contract , LEFt(DATENAME(month,Dt),3) + ' ' + DATENAME(Year,Dt) AS Month_Year_Name ,AmountINTO #TempFROM @t WHERe Dt BETWEEN '01/01/2012' AND '03/31/2012'SELECt @cols = STUFF(( SELECT DISTINCT '],[' + t2.Month_Year_Name FROM #Temp AS t2 ORDER BY '],[' + t2.Month_Year_Name FOR XML PATH('')), 1, 2, '') + ']'SET @query = 'SELECt Id_Contract, ' + @cols + ' FROM ( SELECt Id_Contract , Amount , Month_Year_Name FROM #Temp) x PIVOT ( MAX(amount) FOR Month_Year_Name in (' + @cols + ') ) p 'EXECUTE(@query)DROP TABLE #Temp// 结果
Id_Contract Jan 2012 Mar 20121500 450
编辑
对于您的测试数据，
DECLARE @t TABLE(Id_Contract INT, Dt DATETIME,Amount INT) INSERT INTO @t SELECT 1,'2012-01-01 00:00:00.000',500 INSERT INTO @t SELECT 1,'2012-03-01 00:00:00.000',450 INSERT INTO @t SELECT 2,'2012-03-01 00:00:00.000',450 INSERT INTO @t SELECT 3,'2012-08-01 00:00:00.000',750
输出是
Id_Contract Jan 2012 Mar 20121 500 4502 NULL 450
让我知道它是否满足要求。
