T-SQL Tuesday #18 – CTEs #tsql2sday

 

T-SQL Tuesday

@SQLBob is the volunteer host this month for the T-SQL Tuesday blog party.  Please click on the T-SQL Tuesday logo above to read about: the blog party, the Common Table Expressions (CTEs) topic, and the reason to use the phrase “coefficient of thermal expansion”.  He also volunteered at the SQL Saturday 67 event last March in Chicago.  I was unable to at the last minute so as a substitution I read about the event online and compiled a summary.  This post will be a little light for a number of excuses reasons.  The most important is the find of this amazing CTE article written by @John_Papa called Data Points: Common Table Expressions.

85px-white_flag_icon_svg

I Can’t Follow That!

So as a substitution here is a cheesy script to generate a numbers table using CTEs…

;with
      table_of_ten as
      (
            select 1 as n
            union all
            select n + 1 from table_of_ten where n < 10
      ),
      table_of_hundred as
      (
            select (((tens.n – 1) * 10) + ones.n) as n
            from table_of_ten as tens
            cross join table_of_ten as ones
      ),
      table_of_thousand as (
            select (((hundreds.n – 1) * 100) + ones.n) as n
            from table_of_ten as hundreds
            cross join table_of_hundred as ones
      ),
      table_of_tenthousand as
      (
            select (((thousands.n – 1) * 1000) + ones.n) as n
            from table_of_ten as thousands
            cross join table_of_thousand as ones
      ),
      table_of_hundredthousand as
      (
            select (((tenthousands.n – 1) * 10000) + ones.n) as n
            from table_of_ten as tenthousands
            cross join table_of_tenthousand as ones
      ),
      table_of_million as
      (
            select (((hundredthousands.n – 1) * 100000) + ones.n) as n
            from table_of_ten as hundredthousands
            cross join table_of_hundredthousand as ones
      )

select * from table_of_million order by n;

Advertisements

About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.