Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Columns to rows

Author  Topic 

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-06-06 : 04:19:55
Hi All,

I have a table which is the results of a survey. the table looks something like this:-

Department 1a 1b 1c 2a 2b 2c
test 1 1 1 2 1 2 2
test 2 2 1 1 1 2 1
test 3 1 2 1 1 1 1

i would like the table to look like this:-

Department QuestionNumber Answers
test 1 1a 1
test 2 1a 2
test 3 1a 1
test 1 1b 1
test 2 1b 1
test 3 1b 2
test 1 1c 2
test 2 1c 1
test 3 1c 1

and so on....
could somebody please help!!!!

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 04:25:01
[code]
SELECT *
FROM table t
UNPIVOT (Answers FOR QuestionNumber IN ([1a],[1b],[1c],[2a],[2b],[2c]))u
ORDER BY QuestionNumber,Department

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 04:30:04
To make it dynamic use


DECLARE @questionNumberList varchar(500),@SQL varchar(2000)

SELECT @questionNumberList = STUFF((SELECT ',[' + COLUMN_NAME + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME <> 'Department' AND DATA_TYPE='int'),1,1,'')

SET @SQL = 'SELECT *
FROM table t
UNPIVOT (Answers FOR QuestionNumber IN (' + @@questionNumberList + '))u
ORDER BY QuestionNumber,Department'

EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-06-06 : 05:49:45
Thank you ever so much, really appreciate it .

SELECT *
FROM table t
UNPIVOT (Answers FOR QuestionNumber IN ([1a],[1b],[1c],[2a],[2b],[2c]))u
ORDER BY QuestionNumber,Department

this has worked like a charm.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 06:09:43
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -