query help - 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 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2014-05-05 : 21:20:17

I need a query to get the expected output..



Student:
--------

SID     	     Admission
---               ------------
1                 123456789


StudentDetails:
--------------

SID   MID    Lastname    Fname       Admission
---- ------  ---------  ---------   ------------
1       1       John     Denn        123456789
1       2       John     Camb        123456780


Expected output:


1, john, Denn , 123456789, 2, john, Camb, 123456780

Join condition : Student.SID = StudentDetails.SID

Thanks for your help in advance !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-05 : 21:45:33

the expected output is in 8 columns ?

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

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-06 : 06:23:36

If you want the output in one column then use this


SELECT STUFF( (select   ', '+CASt(sd.MID as VARCHAR(MAX))+' , '+sd.Lastname+' , '+sd.Fname+' , '+CAST(sd.Admission AS VARCHAR(MAX))
FROM         Student  AS s
INNER JOIN   StudentDetails  AS sd
ON s.SID =sd.SID FOR XML PATH('')),1,1,'') AS Result

If you want 8 columns output then use DYNAMIC PIVOT

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

You live only once ..If you do it right once is enough.......

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2014-05-06 : 23:47:22

Thanks

Expected output columns is 8.

Don't need the commas.

Sorry for the confusion

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-07 : 00:53:10

and there will be max 2 MID per 1 SID ?

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

sqlfresher2k7
Aged Yak Warrior

623 Posts