SQL Transform Tree to multidimentational table

Question

I have following database table scheme where I store organization schema.

OrganizationID   Uniqueidentifier 
ParentID         Uniqueidentifier
Name             String

Sample data:

OrganizationID  Name    ParentID
     1            A 
     2            B        1
     3            C        2

I’m expecting to have

Level1  Level2  Level3  Level4  Level5
   1      2       3       ...     ...

How can do this in T-SQL?

Sorry, dont understand your question, where those Level come from? Could you give more description? — ljh, Mar 22 '13 at 06:34
Level actually mean transformed row number, this also represent the hierarchy level of organization. — user846741, Mar 22 '13 at 07:27
[This question](http://stackoverflow.com/questions/5314595/sql-server-to-show-a-data-tree-in-a-specific-format) is very similar to yours. But note that (as shown in that question), if you don't know the number of levels then you need dynamic SQL, so you should also consider generating the output in your reporting tool or a client application instead, it may be easier. — Pondlife, Mar 22 '13 at 14:44

crosan · Answer 1 · 2013-03-22T15:24:28.520

Does this get you what you're going after? Sqlfiddle setup here: http://sqlfiddle.com/#!3/9b107/2

; WITH hierarchy_CTE
AS(
    SELECT OrganizationID, name, parentid, 1 AS Level
    FROM #t1 AS Employee
    WHERE OrganizationID = 4

    UNION ALL

    SELECT boss.OrganizationID, boss.Name, boss.ParentID, Level + 1
    FROM #t1 boss
    INNER JOIN hierarchy_CTE ON boss.OrganizationID = hierarchy_CTE.parentid
    WHERE boss.parentid <> 4 OR boss.parentid IS NULL
  )
, hierarchy2_CTE AS 
(SELECT cte.Level, cte.name
FROM hierarchy_CTE cte)


SELECT * FROM hierarchy2_CTE
PIVOT
(
    MAX(NAME)
    FOR Level IN ([1], [2], [3], [4])
) as pvt

Adapted from PinalDave's article here: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

This basically give you one row based on a given employee (in this case that has an organizationID of 4) and finds their chain of command.

SQL Transform Tree to multidimentational table

1 Answers1