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 2008 Forums
 Transact-SQL (2008)
 Converting Rows into Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chill23
Starting Member

United Kingdom
1 Posts

Posted - 06/17/2014 :  14:40:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/17/2014 :  14:44:39  Show Profile  Reply with Quote
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
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.34 seconds. Powered By: Snitz Forums 2000