Need Urgent Help on this Query

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 2012 Forums

Transact-SQL (2012)

Need Urgent Help on this Query

Author

Topic

amit120k
Starting Member

16 Posts

Posted - 2013-01-04 : 02:04:06

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-04 : 02:48:40

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 03:12:21

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amit120k
Starting Member

16 Posts

Posted - 2013-01-04 : 05:47:07

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-05 : 01:05:27

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources