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 2012 Forums
 Transact-SQL (2012)
 Need Urgent Help on this Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amit120k
Starting Member

India
7 Posts
Posted - 01/04/2013 :  02:04:06  Show Profile  Reply with Quote
I have result set like:

Module Test Question AnswerText QuestionType Correct Answer
-------- ------ -------- ------------- ------------ --------------
Accounts cc Your Name? Hari MCQ 0
Accounts cc Your Name? Sadu MCQ 0
Accounts cc Your Name? Gangu MCQ 0
Accounts cc Your Name? Admin MCQ 1

It should display me like:

Module Test Question Right Ans Option1 Option2 Option3 Option4
-------- ---- ---------- --------- ------- ------- -------- -------
Accounts cc Your Name? Admin Hari Sadu Gangu Admin

Thanks in Advance..

bandi
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 01/04/2013 :  02:48:40  Show Profile  Reply with Quote
SELECT *
FROM (SELECT Module, Test, Question, QuestionType, AnswerText, ROW_NUMBER() OVER(PARTITION BY Module, Test, Question ORDER BY QuestionType) rn FROM YourTable) as pvt
PIVOT (MIN(answerText) FOR rn in ( [1], [2], [3], [4])) as piv

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 01/04/2013 :  03:12:21  Show Profile  Reply with Quote 

SELECT Module,Test,Question,
MAX(CASE WHEN CorrectAnswer=1 THEN AnswerText END) AS RightAns,
MAX(CASE WHEN Seq=1 THEN AnswerText END) AS Option1,
MAX(CASE WHEN Seq=2 THEN AnswerText END) AS Option2,
MAX(CASE WHEN Seq=3 THEN AnswerText END) AS Option3,
MAX(CASE WHEN Seq=4 THEN AnswerText END) AS Option4
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Module,Test,Question ORDER BY Module) AS Seq,* FROM Table)t
GROUP BY Module,Test,Question


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

amit120k
Starting Member

India
7 Posts
Posted - 01/04/2013 :  05:47:07  Show Profile  Reply with Quote
Thanks..dudes.
Both of the solutions worked!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 01/05/2013 :  01:05:27  Show Profile  Reply with Quote
quote:
Originally posted by amit120k

Thanks..dudes.
Both of the solutions worked!!

Welcome

However I dont see RightAns column returned in the first suggestion output!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.03 seconds. Powered By: Snitz Forums 2000