Build a query - 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 2012 Forums

Transact-SQL (2012)

Build a query

Author

Topic

amit120k
Starting Member

16 Posts

Posted - 2013-08-07 : 01:50:23

Q_ID A_ID U_Ans R_Ans Status
--------------------------------------
0006L 000Q2 ans1 ----- Wrong
0006L 000Q3 ---- ----- Wrong
0006L 000Q4 ---- ----- Wrong
0006L 000Q5 ---- test4 Wrong

-----------------------------
(----) in column values are null
Above result should be displayed as
-----------------------------

Q_ID A_ID U_Ans R_Ans Status
--------------------------------------
0006L 000Q2 ans1 test4 Wrong
0006L 000Q3 ---- ----- Wrong
0006L 000Q4 ---- ----- Wrong
0006L 000Q5 ---- ----- Wrong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 06:39:28

[code]
SELECT Q_ID,
A_ID,
CASE WHEN RN=1 THEN MaxU_Ans END AS U_Ans,
CASE WHEN RN=1 THEN MaxR_Ans END AS R_Ans,
Status
FROM
(
SELECT *,
MAX(U_Ans) OVER (PARTITION BY Q_ID) AS MaxU_Ans,
MAX(R_Ans) OVER (PARTITION BY Q_ID) AS MaxR_Ans,
ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY A_ID) AS RN
FROM Table
)t
[/code]

assuming there will be only one value available for U_And and R_Ans per Q_ID group.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources