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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query help

Author  Topic 

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-04-24 : 00:48:32
Hi,

Can you help?

SELECT StudentName, TestID
FROM StudentMaster

Output:
StudentName TestID
Andy 9Passed
Andy 6Failed
Andy 5Failed
Sam 9Passed
Sam 8Passed

How do I update above query so I can have an output like below:
StudentName TestID1 TestID2
Andy 9Passed 6Failed, 5Failed
Sam 9Passed 8Passed

Thank you all

SQLBoy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-24 : 01:57:55
check out PIVOT operator

http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-24 : 02:34:59
Try this ......


DECLARE @temp TABLE (StudentName VARCHAR(50),TestID VARCHAR(50))
INSERT INTO @temp VALUES ('Andy','9Passed')
,('Andy','6Failed')
,('Andy','5Failed')
,('Sam','9Passed')
,('Sam','8Passed')
SELECT StudentName
,[9Passed] AS [TestID1]
,(CASE WHEN [8Passed] IS NULL THEN STUFF((SELECT ','+TestID FROM @temp WHERE TestID NOT IN([9Passed]) AND StudentName LIKE 'Andy' FOR XML PATH('')),1,1,'')ELSE [8Passed] END) AS [TestID2] FROM @temp a
PIVOT(MAX(TestID) FOR TestID IN ([9Passed],[8Passed])) AS PVT


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -