Pivot

Transact-SQL
1

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.

2

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

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

here is an example :

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

insert into @Table
values
('Test1','January',2014,10,08,50),
('Test2','January',2014,62,29,75),
('Test3','January',2014,89,22,30),
('Test1','February',2014,11,65,30),
('Test2','February',2014,52,55,30),
('Test3','February',2014,08,01,30)

--your original table
select * from @Table

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

 [Q1]
,[Q2]
,[Q3]
))t