Multiple CTE’s in a single Query
December 14, 2011 Leave a Comment
When working on a recent presentation on CTE’s I realized that I didn’t have a good generic example of using multiple CTE’s in a single query. Here is what I came up with as a multiple CTE example that is database agnostic.
-- 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;
