December 14, 2011 Leave a Comment
Common Table Expressions are a great way to simplify complex derived table queries. If it makes sense to break out one derived table or subquery into a CTE, it can make sense to do multiple. To use multiple CTE’s in a single query you just need to finish the first CTE, add a comma, declare the name and optional columns for the next CTE, open the CTE query with a comma, write the query, and access it from a CTE query later in the same query or from the final query outside the CTEs.
-- First CTE ;WITH fnames (name) AS (SELECT 'John' UNION SELECT 'Mary' UNION SELECT 'Bill'), -- Second CTE minitials (initial) AS (SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'), -- Third CTE lnames (name) AS (SELECT 'Anderson' UNION SELECT 'Hanson' UNION SELECT 'Jones') -- Using all three SELECT f.name, m.initial, l.name FROM fnames f CROSS JOIN lnames AS l CROSS JOIN minitials m;
Another way that this could be done using SQL Server 2008 or newer is with the row constructor instead of the UNIONs inside of each CTE.
SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x);
This type of row constructor syntax is often used in INSERT statements, but it can also be used in a CTE as shown here:
;WITH Fnames (Name) AS (SELECT x FROM (VALUES ('John'), ('Mary'), ('Bill')) f(x) ), Minitials (initial) AS (SELECT x FROM (VALUES ('A'), ('B'), ('C')) f(x) ), Lnames (Name) AS (SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x) ) SELECT F.Name AS FirstName, m.initial, l.Name AS LastName FROM Fnames F CROSS JOIN Minitials as m CROSS JOIN Lnames as L;
Which produces the following output.
If you found this interesting and would like to learn more about Common Table Expressions, please take a look at my book on CTE’s at Amazon.com.