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 Thursday, August 1, 2013 9:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:28 AM
Points: 138, Visits: 382
How do I nested common table expression?
Post #1480007
Posted Thursday, August 1, 2013 9:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1480012
Posted Wednesday, August 14, 2013 8:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:28 AM
Points: 138, Visits: 382
yes...

Post #1484563
Posted Wednesday, August 14, 2013 9:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:24 PM
Points: 2,954, Visits: 2,557
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)



Post #1484579
Posted Wednesday, August 14, 2013 10:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 3,618, Visits: 5,254
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1484582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse