T-SQL Nested Common Table Expressions

SQLUSA.com

SQL 2008 GRAND SLAM ON 49 CD

SQL Server Training	SQL 2005 Scripts	SQL 2008	Articles
SQL JOBS	News	Format	Developer

How to apply nested CTEs?

Execute the following Microsoft T-SQL example script in SQL Server Management Studio Query Editor to generate the organizational chart for AdventureWorks Cycles fictional bike manufacturer based near Seattle, WA..

------------

-- Microsoft SQL Server T-SQL nested Common Table Expression (CTE)

------------

-- MSSQL tree processing - organizational chart of AdventureWorks Cycles

-- SQL recursive cte

USE AdventureWorks;

-- First inner CTE

WITH cteEmployeePosition(EmployeeID,EmployeeName,ManagerID,Pos)

AS (SELECT EmployeeID,

EmployeeName = c.FirstName + ' ' + c.LastName,

ManagerID,

ROW_NUMBER()

OVER(PARTITION BY ManagerID

ORDER BY c.FirstName + ' ' + c.LastName) AS pos

FROM HumanResources.Employee e

JOIN Person.Contact c

ON e.ContactID = c.ContactID),

-- MSSQL second outer CTE - recursive

cteEmployeeManager(EmployeeID,EmployeeName,ManagerID,LEVEL,MainSortKey)

-- Root (anchor) term

AS (SELECT EmployeeID,

EmployeeName,

ManagerID,

CAST(pos AS VARBINARY(1000))

FROM cteEmployeePosition

WHERE ManagerID IS NULL

UNION ALL

-- Recursive term

SELECT E.EmployeeID,

E.EmployeeName,

E.ManagerID,

M.LEVEL + 1,

CAST(MainSortKey + CAST(E.pos AS BINARY(4)) AS VARBINARY(1000))

FROM cteEmployeePosition AS E

JOIN cteEmployeeManager AS M

ON E.ManagerID = M.EmployeeID)

-- SQL main select - query

SELECT REPLICATE(' ~ ',LEVEL) + '(' + (CAST(EmployeeID AS CHAR(3))) + ') ' +

EmployeeName AS EmployeeName

FROM cteEmployeeManager

ORDER BY MainSortKey;

-- SQL AdventureWorks organizational chart - orgchart

/* Results

(109) Ken Sánchez

~ (273) Brian Welcker

~ ~ (284) Amy Alberts

~ ~ ~ (285) Jae Pak

~ ~ ~ (289) Rachel Valdez

~ ~ ~ (286) Ranjit Varkey Chudukatil

~ ~ (268) Stephen Jiang

~ ~ ~ (283) David Campbell

~ ~ ~ (278) Garrett Vargas

~ ~ ~ (277) Jillian Carson

~ ~ ~ (282) José Saraiva

~ ~ ~ (276) Linda Mitchell

~ ~ ~ (275) Michael Blythe

~ ~ ~ (280) Pamela Ansman-Wolfe

~ ~ ~ (281) Shu Ito

~ ~ ~ (287) Tete Mensa-Annan

~ ~ ~ (279) Tsvi Reiter

~ ~ (288) Syed Abbas

~ ~ ~ (290) Lynn Tsoflias

~ (6 ) David Bradley

~ ~ (119) Jill Williams

~ ~ (271) John Wood

~ ~ (2 ) Kevin Brown

~ ~ (272) Mary Dempsey

~ ~ (106) Mary Gibson

~ ~ (46 ) Sariya Harnpadoungsataya

~ ~ (203) Terry Eminhizer

~ ~ (269) Wanida Benshoof

~ (148) James Hamilton

~ ~ (44 ) A. Scott Wright

~ ~ ~ (170) Alan Brewer

~ ~ ~ (206) Brian LaMee

~ ~ ~ (132) Sairaj Uddin

~ ~ ~ (96 ) William Vong

~ ~ (218) Gary Altman

~ ~ ~ (49 ) Christian Kleinerman

~ ~ ~ ~ (260) Jo Berry

~ ~ ~ ~ (251) Lori Penor

~ ~ ~ ~ (242) Pat Coleman

~ ~ ~ ~ (259) Stuart Macrae

~ ~ ~ (225) Magnus Hedlund

~ ~ (200) Hazem Abolrous

~ ~ ~ (41 ) Peng Wu

~ ~ ~ ~ (145) Andreas Berglund

~ ~ ~ ~ (111) Mark Harrington

~ ~ ~ ~ (77 ) Sean Alexander

~ ~ ~ ~ (253) Sootha Charncherngkha

~ ~ ~ (90 ) Zainal Arifin

~ ~ ~ ~ (212) Chris Norred

~ ~ ~ ~ (161) Karen Berge

~ ~ ~ ~ (127) Sean Chai

~ ~ ~ ~ (54 ) Tengiz Kharatishvili

~ ~ (21 ) Peter Krebs

~ ~ ~ (185) Andrew Hill

~ ~ ~ ~ (10 ) Barry Johnson

~ ~ ~ ~ (230) Bonnie Kearney

~ ~ ~ ~ (19 ) Diane Glimp

~ ~ ~ ~ (17 ) Doris Hartwig

~ ~ ~ ~ (15 ) Jeffrey Ford

~ ~ ~ ~ (8 ) Ruth Ellerbrock

~ ~ ~ ~ (13 ) Sidney Higa

~ ~ ~ (210) Brenda Diaz

~ ~ ~ ~ (31 ) Alejandro McGuel

~ ~ ~ ~ (138) Brian Lloyd

~ ~ ~ ~ (116) Frank Lee

~ ~ ~ ~ (45 ) Fred Northup

~ ~ ~ ~ (215) Gabe Mares

~ ~ ~ ~ (190) Ken Myer

~ ~ ~ ~ (56 ) Kevin Liu

~ ~ ~ ~ (91 ) Lorraine Nay

~ ~ ~ ~ (105) Paula Nartker

~ ~ ~ ~ (81 ) Rajesh Patel

~ ~ ~ ~ (68 ) Shammi Mohamed

~ ~ ~ ~ (152) Tawana Nusbaum

~ ~ ~ (64 ) Cristian Petculescu

~ ~ ~ ~ (224) Betsy Stadick

~ ~ ~ ~ (252) Danielle Tiedt

~ ~ ~ ~ (234) Kimberly Zimmerman

~ ~ ~ ~ (245) Patrick Wedge

~ ~ ~ ~ (262) Tom Vande Velde

~ ~ ~ (135) Cynthia Randall

~ ~ ~ ~ (142) Andy Ruth

~ ~ ~ ~ (110) Jason Watters

~ ~ ~ ~ (33 ) Jian Shuo Wang

~ ~ ~ ~ (196) Michael Vanderhyde

~ ~ ~ ~ (180) Rostislav Shabalin

~ ~ ~ ~ (73 ) Sandra Reátegui Alayo

~ ~ ~ (87 ) David Hamilton

~ ~ ~ ~ (172) Baris Cetinok

~ ~ ~ ~ (63 ) Gary Yukish

~ ~ ~ ~ (99 ) Michael Rothkugel

~ ~ ~ ~ (186) Nicole Holliday

~ ~ ~ ~ (27 ) Paul Komosinski

~ ~ ~ ~ (168) Rob Caron

~ ~ ~ (108) Eric Gubbels

~ ~ ~ ~ (189) Anibal Sousa

~ ~ ~ ~ (232) Hung-Fu Ting

~ ~ ~ ~ (26 ) Ivo Salmre

~ ~ ~ ~ (250) Krishna Sunkammurali

~ ~ ~ ~ (239) Min Su

~ ~ ~ ~ (258) Olinda Turner

~ ~ ~ ~ (115) Paul Singh

~ ~ ~ ~ (237) Prasanna Samarawickrama

~ ~ ~ ~ (151) Samantha Smith

~ ~ ~ ~ (221) Sylvester Valdez

~ ~ ~ (184) Jack Richins

~ ~ ~ ~ (134) Benjamin Martin

~ ~ ~ ~ (24 ) David Johnson

~ ~ ~ ~ (171) David Lawrence

~ ~ ~ ~ (86 ) David Yalovsky

~ ~ ~ ~ (124) Eugene Zabokritski

~ ~ ~ ~ (32 ) Garrett Young

~ ~ ~ ~ (62 ) George Li

~ ~ ~ ~ (209) Jan Miksovsky

~ ~ ~ ~ (204) John Frum

~ ~ ~ ~ (113) Marc Ingle

~ ~ ~ ~ (146) Reed Koch

~ ~ ~ ~ (183) Russell King

~ ~ ~ ~ (50 ) Susan Metters

~ ~ ~ (123) Jeff Hay

~ ~ ~ ~ (155) Alex Nayberg

~ ~ ~ ~ (211) Andrew Cencini

~ ~ ~ ~ (126) Chris Preston

~ ~ ~ ~ (52 ) Kirk Koenigsbauer

~ ~ ~ ~ (88 ) Laura Steele

~ ~ ~ (38 ) Jinghao Liu

~ ~ ~ ~ (36 ) Alice Ciccu

~ ~ ~ ~ (120) Angela Barbariol

~ ~ ~ ~ (179) Chad Niswonger

~ ~ ~ ~ (169) Don Hall

~ ~ ~ ~ (83 ) John Evans

~ ~ ~ ~ (48 ) Jun Cao

~ ~ ~ ~ (207) Kitti Lertpiriyasuwat

~ ~ ~ ~ (69 ) Linda Moschell

~ ~ ~ ~ (192) Michael Entin

~ ~ ~ ~ (141) Michael Patten

~ ~ ~ ~ (107) Mindaugas Krapauskas

~ ~ ~ ~ (58 ) Suroor Fatima

~ ~ ~ (16 ) Jo Brown

~ ~ ~ ~ (57 ) Annik Stahl

~ ~ ~ ~ (157) Brandon Heidepriem

~ ~ ~ ~ (137) Britta Simon

~ ~ ~ ~ (213) Chris Okelberry

~ ~ ~ ~ (247) Ed Dudenhoefer

~ ~ ~ ~ (1 ) Guy Gilbert

~ ~ ~ ~ (162) Jose Lugo

~ ~ ~ ~ (235) Kim Abercrombie

~ ~ ~ ~ (89 ) Margie Shoop

~ ~ ~ ~ (129) Mark McArthur

~ ~ ~ ~ (80 ) Rebecca Laszlo

~ ~ ~ ~ (175) Suchitra Mohan

~ ~ ~ (18 ) John Campbell

~ ~ ~ ~ (219) Charles Fitzgerald

~ ~ ~ ~ (53 ) David Ortiz

~ ~ ~ ~ (208) Jay Adams

~ ~ ~ ~ (226) Karan Khanna

~ ~ ~ ~ (243) Maciej Dusza

~ ~ ~ ~ (254) Michael Zwilling

~ ~ ~ ~ (255) Randy Reeves

~ ~ ~ ~ (174) Steve Masters

~ ~ ~ (7 ) JoLynn Dobney

~ ~ ~ ~ (122) Bryan Baker

~ ~ ~ ~ (156) Eugene Kogan

~ ~ ~ ~ (76 ) James Kramer

~ ~ ~ ~ (84 ) Nancy Anderson

~ ~ ~ ~ (37 ) Simon Rapier

~ ~ ~ ~ (194) Thomas Michaels

~ ~ ~ (182) Katie McAskill-White

~ ~ ~ ~ (125) Barbara Decker

~ ~ ~ ~ (160) John Chen

~ ~ ~ ~ (39 ) Michael Hines

~ ~ ~ ~ (78 ) Nitin Mirchandani

~ ~ ~ ~ (199) Stefen Hesse

~ ~ ~ (74 ) Kok-Ho Loh

~ ~ ~ ~ (202) Elizabeth Keyser

~ ~ ~ ~ (131) Houman Pournasseh

~ ~ ~ ~ (60 ) Jim Scardelis

~ ~ ~ ~ (104) Lolan Song

~ ~ ~ ~ (163) Mandar Samant

~ ~ ~ ~ (95 ) Nuan Yu

~ ~ ~ ~ (43 ) Russell Hunter

~ ~ ~ ~ (165) Sameer Tejani

~ ~ ~ (197) Lori Kane

~ ~ ~ ~ (181) Belinda Newman

~ ~ ~ ~ (23 ) Greg Alderson

~ ~ ~ ~ (136) Kathie Flood

~ ~ ~ ~ (98 ) Scott Gode

~ ~ ~ ~ (22 ) Stuart Munson

~ ~ ~ (173) Michael Ray

~ ~ ~ ~ (97 ) Bjorn Rettig

~ ~ ~ ~ (167) Carol Philips

~ ~ ~ ~ (61 ) Carole Poland

~ ~ ~ ~ (205) Merav Netz

~ ~ ~ ~ (133) Michiko Osada

~ ~ ~ ~ (20 ) Steven Selikoff

~ ~ ~ (85 ) Pilar Ackerman

~ ~ ~ ~ (195) Jimmy Bischoff

~ ~ ~ ~ (72 ) Kim Ralls

~ ~ ~ ~ (121) Matthias Berndt

~ ~ ~ ~ (34 ) Susan Eaton

~ ~ ~ ~ (35 ) Vamsi Kuppa

~ ~ ~ (51 ) Reuben D'sa

~ ~ ~ ~ (222) Brian Goldstein

~ ~ ~ ~ (240) Eric Brown

~ ~ ~ ~ (246) Frank Martinez

~ ~ ~ ~ (257) Jack Creasey

~ ~ ~ ~ (229) Mihail Frintu

~ ~ ~ ~ (249) Patrick Cook

~ ~ ~ ~ (93 ) Ryan Cornelsen

~ ~ ~ ~ (236) Sandeep Kaliyath

~ ~ ~ (159) Shane Kim

~ ~ ~ ~ (75 ) Douglas Hite

~ ~ ~ ~ (112) Janeth Esteves

~ ~ ~ ~ (193) Lionel Penuchot

~ ~ ~ ~ (144) Robert Rounthwaite

~ ~ ~ ~ (40 ) Yvonne McKay

~ ~ ~ (14 ) Taylor Maxwell

~ ~ ~ ~ (67 ) Bob Hohman

~ ~ ~ ~ (153) Denise Smith

~ ~ ~ ~ (118) Diane Tibbott

~ ~ ~ ~ (187) Frank Miller

~ ~ ~ ~ (29 ) Kendall Keil

~ ~ ~ ~ (101) Pete Male

~ ~ ~ (143) Yuhong Li

~ ~ ~ ~ (92 ) Fadi Fakhouri

~ ~ ~ ~ (55 ) Hanying Feng

~ ~ ~ ~ (100) Lane Sacksteder

~ ~ ~ ~ (147) Linda Randall

~ ~ ~ ~ (65 ) Raymond Sam

~ ~ ~ ~ (214) Shelley Dyck

~ ~ ~ ~ (177) Terrence Earls

~ ~ ~ (25 ) Zheng Mu

~ ~ ~ ~ (248) Christopher Hill

~ ~ ~ ~ (220) Ebru Ersan

~ ~ ~ ~ (256) John Kane

~ ~ ~ ~ (228) Kevin Homer

~ ~ ~ ~ (227) Mary Baker

~ (42 ) Jean Trenary

~ ~ (102) Dan Bacon

~ ~ (128) Dan Wilson

~ ~ (117) François Ajenstat

~ ~ (66 ) Janaina Bueno

~ ~ (176) Karen Berg

~ ~ (149) Ramesh Meyyappan

~ ~ (150) Stephanie Conroy

~ ~ ~ (28 ) Ashvini Sharma

~ ~ ~ (188) Peter Connelly

~ (140) Laura Norman

~ ~ (103) David Barber

~ ~ (139) David Liu

~ ~ ~ (178) Barbara Moreland

~ ~ ~ (130) Bryan Walton

~ ~ ~ (94 ) Candy Spoon

~ ~ ~ (59 ) Deborah Poe

~ ~ ~ (166) Dragan Tomic

~ ~ ~ (201) Janet Sheperdigian

~ ~ ~ (216) Mike Seamans

~ ~ (30 ) Paula Barreto de Mattos

~ ~ ~ (191) Grant Culbertson

~ ~ ~ (154) Hao Chen

~ ~ ~ (70 ) Mindy Martin

~ ~ ~ (82 ) Vidur Luthra

~ ~ ~ (47 ) Willis Johnson

~ ~ (71 ) Wendy Kahn

~ ~ ~ (274) Sheela Word

~ ~ ~ ~ (264) Annette Hill

~ ~ ~ ~ (198) Arvind Rao

~ ~ ~ ~ (261) Ben Miller

~ ~ ~ ~ (241) Eric Kurjan

~ ~ ~ ~ (244) Erin Hagens

~ ~ ~ ~ (238) Frank Pellow

~ ~ ~ ~ (231) Fukiko Ogisu

~ ~ ~ ~ (233) Gordon Hee

~ ~ ~ ~ (223) Linda Meisner

~ ~ ~ ~ (164) Mikael Sandberg

~ ~ ~ ~ (266) Reinout Hillmann

~ (12 ) Terri Duffy

~ ~ (3 ) Roberto Tamburello

~ ~ ~ (158) Dylan Miller

~ ~ ~ ~ (79 ) Diane Margheim

~ ~ ~ ~ (114) Gigi Matthew

~ ~ ~ ~ (217) Michael Raheem

~ ~ ~ (9 ) Gail Erickson

~ ~ ~ (11 ) Jossef Goldberg

~ ~ ~ (267) Michael Sullivan

~ ~ ~ (263) Ovidiu Cracium

~ ~ ~ ~ (265) Janice Galvin

~ ~ ~ ~ (5 ) Thierry D'Hers

~ ~ ~ (4 ) Rob Walters

~ ~ ~ (270) Sharon Salavaria

------------