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 2008 Forums
 Transact-SQL (2008)
 query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 05/05/2014 :  21:20:17  Show Profile  Reply with Quote
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)

Singapore
17683 Posts

Posted - 05/05/2014 :  21:45:33  Show Profile  Reply with Quote
the expected output is in 8 columns ?


KH
Time is always against us


Edited by - khtan on 05/05/2014 21:45:47
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
125 Posts

Posted - 05/06/2014 :  06:23:36  Show Profile  Reply with Quote
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.......
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 05/06/2014 :  23:47:22  Show Profile  Reply with Quote
Thanks

Expected output columns is 8.

Don't need the commas.

Sorry for the confusion
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17683 Posts

Posted - 05/07/2014 :  00:53:10  Show Profile  Reply with Quote
and there will be max 2 MID per 1 SID ?


KH
Time is always against us

Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 05/07/2014 :  10:56:34  Show Profile  Reply with Quote
there will more than 2 MID's for 1 SID.I assume max MID could be more than 10..

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.06 seconds. Powered By: Snitz Forums 2000