CTE

Question

Post reply

CTE

vignesh.ms

Hall of Fame

Points: 3456
More actions
August 1, 2013 at 9:49 am

#275839

How do I nested common table expression?

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

vignesh.ms (8/1/2013)
How do I nested common table expression?

To nest CTE just use comma:

;WITH cte1 AS (SELECT .... FROM [sometable])

,cte2 AS (SELECT .... FROM cte1 or/and [sometable] )

,cte3 AS (SELECT .... FROM cte2 or/and cte1 and [sometable] )

Or your question is really about recursive CTEs?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

vignesh.ms

Hall of Fame

Points: 3456

August 14, 2013 at 8:36 pm

#1641629

yes...

happycat59 One Orange Chip Points: 29386 More actions · Answer 3

Have a look at this TechNet article... http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

It shows sample data and the results of a recursive CTE (I think that this is what you are after)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

You may want to also look at the third link in my signature for some other examples of using recursive CTEs.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St