Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTE Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 3:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:38 AM
Points: 10, Visits: 32
Input:
Deptno Empname
10 Niladri
20 Jeeva
30 Deepak
30 Prantik
20 Arina

Output:

Deptno Empname
10 Niladri
20 Jeeva,Arina
30 Deepak,Prantik

I have used CTE for this

WITH
cteReports(Deptno,Empname)
AS
(
SELECT TOP 1 Deptno,Empname FROM #tblemp
UNION ALL
SELECT e.Deptno,e.Empname + ',' + r.Empname
FROM #tblemp e
INNER JOIN cteReports r
ON e.Deptno = r.Deptno
)
SELECT * FROM cteReports
ORDER BY Deptno

But I am getting Error
Post #1606990
Posted Monday, August 25, 2014 3:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
What error are you getting?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1606998
Posted Monday, August 25, 2014 3:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:38 AM
Points: 10, Visits: 32
Incorrect syntax near 'cteReports'.
Post #1607000
Posted Monday, August 25, 2014 3:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Can't see anything wrong.

Make sure that the statement before the CTE (if there is one) is terminated with a ;



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1607002
Posted Monday, August 25, 2014 4:12 AM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:33 AM
Points: 856, Visits: 1,506
CTE will not server the purpose, you will be getting MaxRecursion error because there is no particular hierarchy is available in your table.

following query is written with respect for your desired output:
create table #tblemp (Deptno int, Empname nvarchar(50))

insert into #tblemp
select 10,'Niladri' union all
select 20,'Jeeva' union all
select 30,'Deepak' union all
select 30,'Prantik' union all
select 20,'Arina'

;With cte
AS
(
Select Distinct Deptno
from #tblemp
)


Select e.Deptno , Stuff( (select ', ' + Empname
from #tblemp EE
Where e.Deptno = ee.Deptno
for xml Path ('')),1,2,'')

from cte E


Drop table #tblemp

Hope it helps in case of any query do let us know.
Post #1607003
Posted Monday, August 25, 2014 4:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:38 AM
Points: 10, Visits: 32
Thanks twin.devil
It works fine
Post #1607007
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse