How to get Comma Delimited Values in SQL Server by Grouping?

Question

Hi to all.In my database i've three tables Empl,Items,Orders as follows

EmpId	EmpName
1	Ravi
2	Kumar
3	Sam
4	Telpo

Itemid	ItemDesc
1	keyBoard
2	Mouse
3	Harddrive
4	CableBus

OrderId	EmpId	Itemid
1	1	2
2	1	4
3	2	1
4	2	3

Now i need to display the last table in a report as follows

Empname	ItemDesc
Kumar	CableBus, Harddrive
Ravi	keyboard, Mouse

Can anyone please helpme out for this scenario.

Thanks in Advance.

Jignesh Trivedi · Answer

hi try following query CREATE TABLE #Empl ( EmpId int EmpName varchar (50) ) CREATE TABLE #Items ( Itemid int ItemDesc varchar(50) ) CREATE TABLE #Orders ( OrderId int EmpId int Itemid int ) Insert into #Empl values (1 'Ravi') (2 'Kumar') (3 'Sam') (4 'Telpo') insert into #Items values(1 'keyBoard') (2 'Mouse') (3 'Harddrive') (4 'CableBus') insert into #Orders values (1 12) (214) (321) (423) select e.EmpIde.EmpName(SELECT SUBSTRING((select '' + ItemDesc from #Orders o join #Items i on i.Itemid = o.Itemid where o.EmpId =e.EmpId for xml path(''))22000)) as csv from #Orders o join #Empl e on e.EmpId = o.EmpId group by e.EmpIde.EmpName hope this will help you.