Columns to rows - SQL Server Forums

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

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources