CODEWITHSUNDEEP
sqlsql-servert-sql
Bhaskar
Bhaskar

Reputation: 35

Display all hierarchies in separate columns

I have data in a SQL Server database as shown here:

Source data

I need to write a query which lists all the hierarchy levels as displayed like this:

Target data

How can this be achieved in SQL Server?

This is my SQL script so far:

CREATE TABLE dbo.emphier
(
    Child  varchar(30),
    Parent varchar(30)
)

INSERT INTO dbo.emphier SELECT 'President', NULL
INSERT INTO dbo.emphier SELECT 'Vice President', 'President'
INSERT INTO dbo.emphier SELECT 'CEO', 'Vice President'
INSERT INTO dbo.emphier SELECT 'CTO', 'CEO'
INSERT INTO dbo.emphier SELECT 'Group Project Manager', 'CTO'
INSERT INTO dbo.emphier SELECT 'Project Manager 1', 'Group Project Manager'
INSERT INTO dbo.emphier SELECT 'Project Manager 2', 'Project Manager 1'
INSERT INTO dbo.emphier SELECT 'Team Leader 1', 'Project Manager 2'
INSERT INTO dbo.emphier SELECT 'Software Engineer 1', 'Team Leader 1'
INSERT INTO dbo.emphier SELECT 'Software Engineer 2', 'Software Engineer 1'

I tried this query and it is giving me results but in a different way

WITH cte_org AS (
    SELECT       
        child,parent pl1, null pl2 ,null pl3 , null pl4 , null pl5, null pl6, null pl7, null pl8, null pl9
    FROM       
        dbo.Emphier
    WHERE parent is null
    UNION ALL
    SELECT 
        a.child,a.parent,b.parent,c.parent,d.parent,e.parent,f.parent,g.parent,h.parent, i.parent
    FROM 
        dbo.Emphier a
        inner JOIN Emphier b 
            ON b.Child = a.parent
        left Join emphier c
            ON c.child = b.parent
        left join emphier d
            ON d.child = c.parent
        left join emphier e
            ON e.child = d.parent
        left join emphier f
            ON f.child = e.parent
        left join emphier g
            ON g.child = f.parent
        left join emphier h
            ON h.child = g.parent
        left join emphier i
            ON i.child = h.parent
)
SELECT * FROM cte_org;

Results

Any help is appreciated

Upvotes: 0

Views: 320

Answers (1)

Squirrel
Squirrel

Reputation: 24763

Here is one way, it uses recursive cte.

In the anchor member of the rcte, Level starts with 1 and increment as it travels down the hierarchy. Based on the Level, the case expression return the child to the P1 to P10

with rcte as
(
    select  Child, Parent, Level = 1, 
            P1 = Child, 
            P2 = convert(varchar(30), ''),
            P3 = convert(varchar(30), ''),
            P4 = convert(varchar(30), ''),
            P5 = convert(varchar(30), ''),
            P6 = convert(varchar(30), ''),
            P7 = convert(varchar(30), ''),
            P8 = convert(varchar(30), ''),
            P9 = convert(varchar(30), ''),
           P10 = convert(varchar(30), '')
    from    emphier
    where   Parent  = ''

    union all

    select  e.Child, e.Parent, Level = r.Level + 1, 
            P1 = r.P1, 
            P2 = case when r.Level + 1 = 2 then e.Child else r.P2 end,
            P3 = case when r.Level + 1 = 3 then e.Child else r.P3 end,
            P4 = case when r.Level + 1 = 4 then e.Child else r.P4 end,
            P5 = case when r.Level + 1 = 5 then e.Child else r.P5 end,
            P6 = case when r.Level + 1 = 6 then e.Child else r.P6 end,
            P7 = case when r.Level + 1 = 7 then e.Child else r.P7 end,
            P8 = case when r.Level + 1 = 8 then e.Child else r.P8 end,
            P9 = case when r.Level + 1 = 9 then e.Child else r.P9 end,
            P10 = case when r.Level + 1 = 10 then e.Child else r.P10 end
    from    rcte r
            inner join emphier e    on  r.Child = e.Parent
)
select  *
from    rcte

Upvotes: 1

Related Questions