Converting Rows into Columns - 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 2008 Forums

Transact-SQL (2008)

Converting Rows into Columns

Author

Topic

chill23
Starting Member

1 Post

Posted - 2014-06-17 : 14:40:49

I'd like to create a report to display all the answers to a form on one line with each question as a separate column.

Here is the table:

ID Form_ID QuestionID AnswerValue
1 1 123 Paul
2 1 124 Smith
3 1 125 18
4 2 123 Mary
5 2 124 Samsonite
6 2 125 24

Here is what I'd like it to be

Form_ID Firstname Surname Age
1 Paul Smith 18
2 Mary Samsonite 24

Please note how I have changed the questionid tag to be more meaningful.

Any help on this will be much appreciated.

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-17 : 14:44:39

You can use a Pivot or a Aggregate function with a CASE expression. Here is a quick example of how to use a CASE expression:

SELECT
	MAX(CASE WHEN QuestionID = 123 THEN AnswerValue ELSE NULL END) AS FirstName
	,MAX(CASE WHEN QuestionID = 124 THEN AnswerValue ELSE NULL END) AS LastName
	,...
FROM
	<TableName>
GROUP BY
	Form_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources