How to Solve this Multiple value for single output - Bandi [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 3/30/2015 | Points: 10 | Views : 384 | Status : [Member] [MVP] | Replies : 3
Hi
Bandi

My Table Structure with data like this


Create Table Course
(
CourseId int Primary key Identity(1,1),
CourseName Varchar(50)
)
Insert into Course values('C#')
Insert into Course values('Asp.net')
Insert into Course values('Sqlserver')
Insert into Course values('MySql')

Create Table Students
(
StudentId int Primary key identity(1,1),
StudentName varchar(30)
)
Insert into Students values('John')
Insert into Students values('David')
Insert into Students values('Hendry')
Insert into Students values('Smith')
Insert into Students values('Watson')



Create Table CourseAllot
(
AllotId int Primary key identity(1,1),
CourseId int,
StudentId int
)

Insert into CourseAllot values (1,1)
Insert into CourseAllot values (1,1)
Insert into CourseAllot values (2,1)
Insert into CourseAllot values (1,2)
Insert into CourseAllot values (3,4)
Insert into CourseAllot values (3,5)

--I need Output

Sno Course Name Student Name
1 C# John,Hendry,David
2 Asp.net John
3 Sqlserver Smith,WatSon

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 3/30/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

;With CourseAllocation AS
(SELECT c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT Row_Number() OVER( ORDER BY CourseName) Sno, CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/30/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Bandi
Good One I have lot of queries . can you send test mail for me.I send mail to u. can you solve and send me.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/30/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
bandi

I need show this fields also CourseId,AllotId need

check this query but i got error can u solve this

;With CourseAllocation AS
(SELECT c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT Row_Number() OVER( ORDER BY CourseName) Sno,CourseId,AllotId,CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseName



Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response