LinkedIn

Saturday, 12 January 2008

Create SQL 2005 pivot table

CREATE TABLE #FinalTable
(
[Name] varchar(10),
[Month] varchar(10),
amount float
)


Insert into #finaltable values ('Ray','Jan',1)
Insert into #finaltable values ('Ray','Feb',2)
Insert into #finaltable values ('Ray','Mar',3)

Insert into #finaltable values ('Eimaer','Jan',5)
Insert into #finaltable values ('Eimaer','Feb',6)
Insert into #finaltable values ('Eimaer','Mar',7)

select
[Name],
isnull(sum([Jan]),0) as 'Jan',
isnull(sum([Feb]),0) as 'Feb',
isnull(sum([Mar]),0) as 'MAr'
from #FinalTable
AS Data PIVOT(
SUM( amount)
FOR [Month] IN
([Jan],[Feb],[Mar])

) AS PVT

group by [Name]

drop table #FinalTable