SQLTeam.com | Weblogs | Forums




Hope some one can help me with this one. I have a table with the headers of Survey,Month,Year,Q1,Q2,Q3 etc to Q30. What I am after is to create a table that has the following headers Survey, Month,Year,Question,Score can any one advise how to do this, I just can't get my head round how the Pivot script should read.


You need UNPIVOT rather than PIVOT. It would be something like this:

FROM YourTable
( Score FOR Question IN ([Q1],[Q2],[Q3],[Q30]))U


here is an example :

declare @Table table (Survey varchar(50),[month] varchar(15),[Year] int, Q1 int,Q2 int,Q3 int)

insert into @Table

--your original table
select * from @Table

select * from @Table unpivot( scores for questions in (