{"id":3532,"date":"2012-04-24T03:29:27","date_gmt":"2012-04-24T03:29:27","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/hierarchical-queries-with-common-table-expressions\/"},"modified":"2016-07-28T10:50:46","modified_gmt":"2016-07-28T10:50:46","slug":"hierarchical-queries-with-common-table-expressions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/hierarchical-queries-with-common-table-expressions\/","title":{"rendered":"Hierarchical Queries with Common Table Expressions"},"content":{"rendered":"

I wanted a simple example of a hierarchical query using a common table expression in SQL Server.  MSDN had an example called Recursive Queries Using Common Table Expressions<\/a>, but it relied on the Adventure Works database and was joining across three different tables.  Then I stumbled upon another blog called Working with hierarchical data in SQL Server databases<\/a> that had all of the queries to make the tables and populate the data, but it was using stored procedures.  So I took the liberty of putting them together into a very simple example that you can run:<\/p>\n

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Emp]’) AND type in (N’U’))
BEGIN
    — Create the table
    CREATE TABLE dbo.Emp
    (
        EmpID        int    PRIMARY KEY,
        EmpName        varchar(30),
        MgrID        int    FOREIGN KEY REFERENCES Emp(EmpID)
    )
   
    — Create a non-clustered index for query performance
    CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
   
    — Populate the table with data
    INSERT dbo.Emp SELECT 1, ‘President’, NULL
    INSERT dbo.Emp SELECT 2, ‘Vice President’, 1
    INSERT dbo.Emp SELECT 3, ‘CEO’, 2
    INSERT dbo.Emp SELECT 4, ‘CTO’, 2
    INSERT dbo.Emp SELECT 5, ‘Group Project Manager’, 4
    INSERT dbo.Emp SELECT 6, ‘Project Manager 1’, 5
    INSERT dbo.Emp SELECT 7, ‘Project Manager 2’, 5
    INSERT dbo.Emp SELECT 8, ‘Team Leader 1’, 6
    INSERT dbo.Emp SELECT 9, ‘Software Engineer 1’, 8
    INSERT dbo.Emp SELECT 10, ‘Software Engineer 2’, 8
    INSERT dbo.Emp SELECT 11, ‘Test Lead 1’, 6
    INSERT dbo.Emp SELECT 12, ‘Tester 1’, 11
    INSERT dbo.Emp SELECT 13, ‘Tester 2’, 11
    INSERT dbo.Emp SELECT 14, ‘Team Leader 2’, 7
    INSERT dbo.Emp SELECT 15, ‘Software Engineer 3’, 14
    INSERT dbo.Emp SELECT 16, ‘Software Engineer 4’, 14
    INSERT dbo.Emp SELECT 17, ‘Test Lead 2’, 7
    INSERT dbo.Emp SELECT 18, ‘Tester 3’, 17
    INSERT dbo.Emp SELECT 19, ‘Tester 4’, 17
    INSERT dbo.Emp SELECT 20, ‘Tester 5’, 17
END
GO    <\/p>\n

–Hierarchical Query using Common Table Expressions
WITH ReportingTree (EmpID, EmpName, MgrID, Lvl)
AS
(
    –Anchor Member
    SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL
    UNION ALL
    –Recusive Member
    SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1
    FROM emp INNER JOIN ReportingTree
        on emp.MgrID = ReportingTree.EmpID
)
SELECT * FROM ReportingTree<\/p>\n

You will, however, need to refer to the MSDN documentation link to see how it’s working.<\/p>\n","protected":false},"excerpt":{"rendered":"

I wanted a simple example of a hierarchical query using a common table expression in SQL Server.  MSDN had an example called Recursive Queries Using Common Table Expressions, but it relied on the Adventure Works database and was joining across three different tables.  Then I stumbled upon another blog called Working with hierarchical data in……<\/p>\n","protected":false},"author":46738,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-3532","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3532","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/46738"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3532"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3532\/revisions"}],"predecessor-version":[{"id":42150,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3532\/revisions\/42150"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3532"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3532"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3532"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}