Solution to TSQL Beginners Challenge 21

DECLARE @t TABLE(Deptno INT, Empname VARCHAR(20)) 
INSERT INTO @t  
SELECT 10, 'Niladri' UNION ALL 
SELECT 20, 'Jeeva' UNION ALL 
SELECT 30, 'Deepak' UNION ALL 
SELECT 30, 'Priyanka' UNION ALL 
SELECT 20, 'Arina'  
 
--SELECT * FROM @t 
 
Select DeptNo,Right(EmpName,Len(EmpName)-1) 'EmpName' from  
( 
Select  
DeptNo, 
Rtrim(Ltrim((Select '~'+Coalesce(B.Empname,'') From @T B Where A.DeptNo = B.DeptNo FOR XML PATH( '' ) ))) 'EmpName' 
From @T A 
Group By DeptNo 
)A 
Order BY DeptNo, EmpName 

Tags: