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 1, 2013 9:49 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, July 6, 2015 1:28 AM Points: 188, Visits: 516
 How do I nested common table expression?
Post #1480007
 Posted Thursday, August 1, 2013 9:58 AM
 SSCrazy Group: General Forum Members Last Login: Monday, November 28, 2016 10:12 AM Points: 2,934, Visits: 5,475
 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: Monday, July 6, 2015 1:28 AM Points: 188, Visits: 516
 yes...
Post #1484563
 Posted Wednesday, August 14, 2013 9:56 PM
 Hall of Fame Group: General Forum Members Last Login: Thursday, November 24, 2016 9:38 PM Points: 3,526, Visits: 3,061
 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: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1484582

 Permissions