Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 CTE Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 01, 2013 9:49 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 9:47 AM Points: 120, Visits: 317
 How do I nested common table expression?
Post #1480007
 Posted Thursday, August 01, 2013 9:58 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 03, 2014 10:10 AM Points: 2,792, Visits: 4,874
 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 Group: General Forum Members Last Login: Today @ 9:47 AM Points: 120, Visits: 317
 yes...
Post #1484563
 Posted Wednesday, August 14, 2013 9:56 PM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 9:21 PM Points: 2,842, Visits: 2,423
 Have a look at this TechNet article... http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspxIt 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 Group: General Forum Members Last Login: Yesterday @ 6:03 PM Points: 3,590, Visits: 5,098
 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

 Permissions