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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the Common Table Expression?

Microsoft SQL Server T-SQL Common Table Expression (CTE) is an alternative for using temporary tables or views. It is a temporary table result set whose scope is limited to the execution of a SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. CTE is a virtual table which can be referenced several times in the associated query. It can also be self-referenced hence recursive. Here are two examples:

USE AdventureWorks;

 

WITH cteSupervisor(ManagerID, StaffCount)

AS

(

      SELECT ManagerID, COUNT(*)

      FROM HumanResources.Employee AS e

      GROUP BY ManagerID

)

SELECT Manager=LEFT(FirstName,1)+'. ' +LastName,

      e.Title, StaffCount

FROM cteSupervisor s

JOIN HumanResources.Employee e

      ON s.ManagerID = e.EmployeeID

JOIN Person.Contact c

      ON c.ContactID = e.ContactID

ORDER BY LastName

GO

/*

Manager     Title                                     StaffCount

S. Abbas    Pacific Sales Manager                     1

H. Abolrous Quality Assurance Manager                 2

P. Ackerman Shipping and Receiving Supervisor         5

A. Alberts  European Sales Manager                    3

*/

 

-- AdventureWorks Cycles company organization chart

-- Recursive Common Table Expression - Recursive CTE

WITH cteOrgChart

     (LastName, FirstName, Title, ManagerID, EmployeeID, HierarchyLevel)

AS

(

   -- Anchor part

   SELECT

      LastName,

      FirstName,

      e.Title,

      ManagerID,

      EmployeeID,

      1 as HierarchyLevel

   FROM HumanResources.Employee e

   INNER JOIN Person.Contact c

            ON c.ContactID = e.ContactID

   WHERE ManagerID IS NULL  -- CEO

 

   UNION ALL

 

   -- Recursive part

   SELECT

      c.LastName,

      c.FirstName,

      e.Title,

      e.ManagerID,

      e.EmployeeID,

      oc.HierarchyLevel + 1 AS HierarchyLevel

   FROM HumanResources.Employee e

     INNER JOIN cteOrgChart oc

            ON  e.ManagerID = oc.EmployeeID

     INNER JOIN Person.Contact c

            ON c.ContactID = e.ContactID

)

 

SELECT Name = FirstName+' '+LastName,

       Title,

       MgrID = ManagerID,

       EmpID = EmployeeID,

       HierarchyLvl = HierarchyLevel

FROM cteOrgChart

ORDER BY HierarchyLevel, LastName, FirstName

/* Partial results

 

Name              Title                         MgrID EmpID HierarchyLvl

Ken Sánchez       Chief Executive Officer       NULL  109         1

David Bradley     Marketing Manager             109   6           2

Terri Duffy       Vice President of Engineering 109   12          2

James Hamilton    Vice President of Production  109   148         2

Laura Norman      Chief Financial Officer       109   140         2

Jean Trenary      Information Services Manager  109   42          2

Brian Welcker     Vice President of Sales       109   273         2

Syed Abbas        Pacific Sales Manager         273   288         3

*/

Related articles:

Using Common Table Expressions

Common Table Expressions

 

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