How to Solve this Sql Query

SELECT EmpId, STUFF((SELECT ','+EmpName FROM Table2 WHERE EmpId=t1.EmpId FOR XML PATH('') ), 1,1, '') EmpName
FROM Table2 t1
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 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

DECLARE @Course table(CourseId int Primary key identity(1,1),CourseName Varchar(50));
DECLARE @Students table(StudentId int Primary key identity(1,1),StudentName Varchar(50));
DECLARE @CourseAllot table(AllotId int Primary key identity(1,1),CourseId int,StudentId int);

-- Insert Records
Insert into @Course values('C#'),('Asp.net'),('Sqlserver'),('MySql')
Insert into @Students values('John'),('David'),('Hendry'),('Smith'),('Watson')
Insert into @CourseAllot values (1,1),(1,3),(2,1),(1,2),(3,4),(3,5)

;WITH CTE as(
SELECT 
	
	c.CourseName,
	s.StudentName,
	ca.CourseId,
	ca.StudentId
FROM @Course c 
JOIN @CourseAllot ca on c.CourseId  = ca.CourseId
JOIN @Students s on s.StudentId = ca.StudentId),
CTE2 AS(
SELECT 
	distinct  
	c1.CourseName,	
	STUFF((SELECT ','+StudentName FROM Cte c WHERE c.CourseId=c1.CourseId FOR XML PATH('') ), 1,1, '') StudentName
FROM Cte c1)
SELECT 
	Slno = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
	,CourseName [Course Name]
	,StudentName [Student Name]
FROM CTE2

RESULT
-----------

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

--
Thanks & Regards,
RNA Team

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

Hi
Bandi
Thanks your reply here my table have 2 separate table so how to achieve this

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

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