How to Change this Query following output - Bandi [Resolved]

Posted by Jayakumars under ASP.NET AJAX on 3/30/2015 | Points: 10 | Views : 427 | Status : [Member] [MVP] | Replies : 1
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 also AllotId,CourseId need Bandi


Sno Course AllotId,CourseId Name Student Name
1 C# 1 1 John,Hendry,David
2 Asp.net 3 2 John
3 Sqlserver 5 3 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 ca.AllotId, ca.CourseId, c.CourseName, s.StudentName
FROM CourseAllot ca
JOIN Course c ON c.CourseId = ca.CourseId
JOIN Students s ON s.StudentId = ca.StudentId )
SELECT DISTINCT CourseId, CourseName,
Min(AllotId)OVER(PARTITION BY CourseId ) AllotId,
STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1


CourseId CourseName AllotId StudentNames
1 C# 1 John,John,David
2 Asp.net 3 John
3 Sqlserver 5 Smith,Watson


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

Login to post response