SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Ye
这是一个旋转sql,只有事先知道他的值是1,2,3,4时才能完全找出所有的资料,假若这个值是动态的,这个sql该如何来写呢?
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Ye
这是一个旋转sql,只有事先知道他的值是1,2,3,4时才能完全找出所有的资料,假若这个值是动态的,这个sql该如何来写呢?
Set @S=''
Select @S=@S+',SUM(CASE Quarter WHEN '+Rtrim([Quarter])+' THEN Amount ELSE 0 END) AS Q'+Rtrim([Quarter])
From Northwind.dbo.Pivot Group By [Quarter] Order By [Quarter]
Select @S='SELECT [Year]'+@S+' FROM Northwind.dbo.Pivot GROUP BY Ye'
EXEC(@S)
declare @s varchar(2000)
select @s='SELECT Year'
select @s=@s+',[Q'+Rtrim(quarter)+']=sum( case Quarter when '+Rtrim(Quarter)+' then Amount else 0 end' from Northwind.dbo.Pivot
select @s=@s+' from Northwind.dbo.Pivot group by Year'
exec(@s)