Hierarchical query to get the following output from a query in sql server

Question

i have a table called emp. The table has following data.

empid  name Manager_id
1       A   Null
2       B   1
3       C   2
4       D   2
5       E   4

i want the output from above table as below.

empid   name Manager_id Level1 Level2 Level3 Level4
1         A   null      A      null    null  null
2         B    1        A      B       null  null
3         C    2        A      B       C     null
4         D    2        A      B       D     D
5         E    4        A      B       D     E

Try searching BOL with "recursive CTE", I think you might find just the above example you describe.. — NickyvV, May 27 '14 at 12:53
Does it need to be a single query or can we use temp-tables (or worst case table-variables) ? — deroby, May 30 '14 at 17:13
@Axs, Is there an error in column Level4. For 4th Row, the value should be NULL, ist it? As of Now it is D. — Jithin Shaji, Jun 02 '14 at 05:14
@Axs,i tried to solve similar problem here, http://stackoverflow.com/questions/20841464/hierarchy-relationship-sql-query/20843395#20843395 — KumarHarsh, Jun 02 '14 at 11:39

Mikael Eriksson · Answer 1 · 2014-06-02T12:55:32.683

with C as
(
  select T.EmpID,
         T.ManagerID,
         T.Name,
         cast('' as xml).query('element X { attribute V {sql:column("T.Name")}}') as LvlXML
  from YourTable as T
  where T.ManagerID is null
  union all
  select T.EmpID,
         T.ManagerID,
         T.Name,
         C.LvlXML.query('., element X { attribute V {sql:column("T.Name")}}')
  from YourTable as T
    inner join C 
      on T.ManagerID = C.EmpID
)
select C.EmpID,
       C.Name,
       C.ManagerID,
       C.LvlXML.value('/X[1]/@V', 'varchar(100)') as Level1,
       C.LvlXML.value('/X[2]/@V', 'varchar(100)') as Level2,
       C.LvlXML.value('/X[3]/@V', 'varchar(100)') as Level3,
       C.LvlXML.value('/X[4]/@V', 'varchar(100)') as Level4,
       C.LvlXML.value('/X[5]/@V', 'varchar(100)') as Level5
from C;

SQL Fiddle

Update:

@t-clausen.dk pointed out that performence for the query above is not what it can be so here is a faster version.

First add an index on ManagerID with Name as an included column.

create index IX_YourTable_ManagerID on YourTable(ManagerID) include(Name)

And the new query that builds the needed columns as we go in the recursion.

with C as
(
  select T.EmpID,
         T.ManagerID,
         T.Name,
         T.Name as Level1,
         cast(null as varchar(100)) as Level2,
         cast(null as varchar(100)) as Level3,
         cast(null as varchar(100)) as Level4,
         1 as Lvl
  from YourTable as T
  where T.ManagerID is null
  union all
  select T.EmpID,
         T.ManagerID,
         T.Name,
         C.Level1,
         case when C.lvl = 1 then T.Name else C.Level2 end,
         case when C.lvl = 2 then T.Name else C.Level3 end,
         case when C.lvl = 3 then T.Name else C.Level4 end,
         C.Lvl + 1
  from YourTable as T
    inner join C 
      on T.ManagerID = C.EmpID
)
select C.EmpID,
       C.Name,
       C.ManagerID,
       C.Level1,
       C.Level2,
       C.Level3,
       C.Level4
from C;

That gives you this nice little query plan with an index seek both in the anchor and in the recursive part of the query:

enter image description here

SQL Fiddle

hi Mikael Eriksson, very fancy answer. I am sure including this to my learning notes. I am wondering though, if this isn't overkill. I would think buliding up the LvlXML will be slower and more memory consuming than a regular PIVOT like my answer — t-clausen.dk, Jun 02 '14 at 11:59
@t-clausen.dk Yes, yours is faster and requires less memory but has higher number of reads. [Here is a version](http://sqlfiddle.com/#!3/2df664/10) that beats the crap out of both our versions. No memory grant (that I can see), less reads than my version and faster than your version. (I have only done limited testing). — Mikael Eriksson, Jun 02 '14 at 12:41
@t-clausen.dk and use this index to spice things up even further `create index IX_YourTable_ManagerID on YourTable(ManagerID) include(Name)` — Mikael Eriksson, Jun 02 '14 at 12:45
nice way of avoiding the issue of where the data for the pivot is from (an issue I had in my version). Interesting way of rotating the names. — t-clausen.dk, Jun 02 '14 at 13:18

t-clausen.dk · Answer 2 · 2014-06-02T09:08:36.117

I would use PIVOT, I imagine this will offer the best performance.

-- testtable and data
DECLARE @t table(Empid int identity(1,1),  Name char(1), Manager_id int)
INSERT @t VALUES('A',Null),('B',1),('C',2),('D',2),('E',4)

-- query
;WITH CTE as
(
  SELECT 
    Name,
    Manager_id,
    Name lvlName,
    Empid,
    1 reverselvl
  FROM @t
  UNION ALL
  SELECT 
    CTE.Name,
    T.Manager_id,
    T.Name lvlName,
    CTE.Empid,
    CTE.reverselvl + 1
  FROM @t T
  JOIN CTE
  ON T.Empid = CTE.Manager_id
), 
CTE2 as
(
  SELECT Empid, 
  count(*) over (partition by Empid) - reverselvl lvl,
  lvlName, 
  max(Manager_id) over (partition by Empid) Manager_id,
  CTE.Name
  FROM CTE
)
SELECT 
  Empid, 
  Name, 
  Manager_id, 
  [0] Level1, [1] Level2, [2] Level3, [3] Level4
FROM CTE2
PIVOT (max(lvlName) FOR [lvl] IN ([0],[1],[2],[3])) AS pvt 
OPTION (maxrecursion 0)

Result:

Empid Name Manager_id Level1 Level2 Level3 Level4
1     A    NULL       A      NULL   NULL   NULL
2     B    1          A      B      NULL   NULL
3     C    2          A      B      C      NULL
4     D    2          A      B      D      NULL
5     E    4          A      B      D      E

Hierarchical query to get the following output from a query in sql server

2 Answers2