datetime century date into pad dynamic cursor money percent sp job isnumeric isdate over update
SQLUSA.com
SQL 2008 GRAND SLAM ON 49 CD
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
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,

                0,

                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;

GO

-- 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

 */

 

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

Related link:

http://msdn.microsoft.com/en-us/magazine/cc163346.aspx

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page SQL Server Training at www.sqlusa.com.
Microsoft SQL Server 2012 Training Videos at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Accounting
Administrative
Advertising
Arts
Architecture
Banking
Business Intelligence
Career Jobs
Celebrity
Computer
Consulting
Customer Service
Education
Engineering
Entertainment
Entry Level
Executive
Federal
Finance
Government
Hardware
Healthcare
Hospital
Human Resources
Information Technology
Insurance
Internet
Job Openings
Laboratory
Law Enforcement
Legal
Logistics
Manufacturing
Marketing
Medical
Military
Nursing
Pharmaceutical
Physician
Public Relations
Publishing
Real Estate
Restaurant
Retail
Sales
Social Media
Software
SQL Database
Telecomm
Therapist
Training
Transportation
Truck Driver
Travel
Web
Work from Home

FREE SS SQL / BI OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011 Microsoft Community Contributor 2012

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts

JOIN US ON TWITTER

Copyright 2005-2012, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.