How to solve this query Bandi [Resolved]

;WITH Cte AS (
SELECT t2.EmpId, t2.EmpName
FROM Table1 t1
JOIN Table2 t2 ON T1.EmpId = t2.EmpId)
SELECT EmpId, STUFF((SELECT ','+EmpName FROM Cte WHERE EmpId=c1.EmpId FOR XML PATH('') ), 1,1, '') EmpName
FROM Cte c1
GROUP BY EmpId

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

;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

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

Kumaraspcode2009@gmail.com

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

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

Kumaraspcode2009@gmail.com

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

hi
bandi

I need 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

Kumaraspcode2009@gmail.com

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

you can add CourseId to the output, but not AllotID...

;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 CourseId,  CourseName, STUFF((SELECT ','+StudentName FROM CourseAllocation WHERE CourseName=c1.CourseName FOR XML PATH('')),1,1,'') StudentNames
FROM CourseAllocation c1
GROUP BY CourseId,CourseName 

My question for allotId is that , what is your expected output if you want AllotID in the result?

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