DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE