SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Columns to rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Muj9
Starting Member

United Kingdom
36 Posts

Posted - 06/06/2013 :  04:19:55  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/06/2013 :  04:25:01  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/06/2013 04:26:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/06/2013 :  04:30:04  Show Profile  Reply with Quote
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
Starting Member

United Kingdom
36 Posts

Posted - 06/06/2013 :  05:49:45  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/06/2013 :  06:09:43  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000