CTE

Question

CTE

sunitha.yanagandala

SSC Enthusiast

Points: 144
More actions
August 25, 2014 at 3:19 am

#311024

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What error are you getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sunitha.yanagandala SSC Enthusiast Points: 144 More actions · Answer 2

sunitha.yanagandala

SSC Enthusiast

Points: 144

August 25, 2014 at 3:54 am

#1740647

Incorrect syntax near 'cteReports'.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

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.

sunitha.yanagandala SSC Enthusiast Points: 144 More actions · Answer 5

sunitha.yanagandala

SSC Enthusiast

Points: 144

August 25, 2014 at 4:35 am

#1740653

Thanks twin.devil

It works fine