SQL Server Query or Tool to show Hierarchical Data

Question

We have a general organizational table structure, think of it s a Tree or Pyramid Hierarchy. We basically have multiple "trees" we want to show. On for one company, one for another ETC.

Does anyone know of a good way to display this data? SQL Query would be nice, doubt it would be possible, but I wouldn't be against using some OTS tool (preferably free). I would also like to avoid some type of report. I don't need an actual solution just need to know if its possible. So if you say SQL if you can give me a 2 table example of showing a root a leave I would be happy.

Structure is pretty general

And each table is linked through a surrogate key CompanyID, CompanyGroupID, etc.

Any suggestions on ways we could display/query for this data? Last resort is to write a quick C# Windows Application...

We would like to see it in tree form:

--                      1-Company
--                     /        \
--             CompanyGroupA   CompanyGroupB
--            /       \              \
--  CompanyStoreA1 CompanyStoreA1 CompanyStoreB
--    /      \            /    \
--Employee   A            B     C   

In attempt to please the masses here is an example test script to populate the query.

DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  


DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets',1 ) 
INSERT @CompanyGroup VALUES (2,'Humans',1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics',2 ) 
INSERT @CompanyGroup VALUES (4,'Food',2 ) 


DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA',1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB',1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC',1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD', 1) 
INSERT @CompanyStore VALUES (5,'HumansStore',2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore',3 ) 

The final solution was pretty awesome I modified the usp_DrawTree to accept varchar vs ints because i had to make my query ids unique. I then just did a select/union all and built the parent child relationship.

select * into #TreeData from (
  select ID='C' + cast(id as varchar(10)),
       ParentID=null,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from Company c 
 )
union all (
  select ID='CG' + cast(id as varchar(10)),
       ParentID=cg.CompanyID ,
       DataForBox=name + '(' + cast(id as varchar(10)) + ')',
       ExtraInfo='', 
       SortColumn=name
  from CompanyGroup cg join Company c on c.ID=cg.CompanyID 
  ) 
//union all rest of hierarchy
)

Answer 1

Brad Schulz to the rescue in the form of usp_DrawTree.

Answer 2

you don't provide any table structure, so here is a sample of a recursive CTE processing a tree structure:

--go through a nested table supervisor - user table and display the chain
DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /        \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /       \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /      \            /    \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  char(4)

--get complete tree---------------------------------------------------
SET @Root_id=null
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree


--get all below 2---------------------------------------------------
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

--get all below 6---------------------------------------------------
SET @Root_id=6
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

OUTPUT:

@Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
9      Bill       1             1          Jerome             2
10     Sam        9             9          Bill               3
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(10 row(s) affected)

@Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

@Root_id='6   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
6      David      2             2          Joe                1
7      Ian        6             6          David              2
8      Helen      6             6          David              2

(3 row(s) affected)

EDIT based on OP's given tables and data:

try something like this:

SET NOCOUNT ON
DECLARE @Company table (id int, name varchar(40) )
INSERT @Company VALUES (1,'Living Things' )  
INSERT @Company VALUES (2,'Boring Company' )  

DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int)
INSERT @CompanyGroup VALUES (1,'Pets'        ,1 ) 
INSERT @CompanyGroup VALUES (2,'Humans'      ,1 ) 
INSERT @CompanyGroup VALUES (3,'Electronics' ,2 ) 
INSERT @CompanyGroup VALUES (4,'Food'        ,2 ) 

DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int)
INSERT @CompanyStore VALUES (1,'PetsStoreA'   ,1 ) 
INSERT @CompanyStore VALUES (2,'PetsStoreB'   ,1 ) 
INSERT @CompanyStore VALUES (3,'PetsStoreC'   ,1 ) 
INSERT @CompanyStore VALUES (4,'PetsStoreD'   ,1) 
INSERT @CompanyStore VALUES (5,'HumansStore'  ,2 ) 
INSERT @CompanyStore VALUES (6,'FoodStore'    ,3 ) 

--not provided by the OP, so I made it up
DECLARE @CompanyEmployees table (id int, name varchar(10), reports_to_id int, CompanyStoreID int)
INSERT @CompanyEmployees VALUES (1,'Jerome', NULL ,1)  -- tree is as follows:
INSERT @CompanyEmployees VALUES (2,'Joe'   ,1     ,1)     --                      PetsStoreA             PetsStoreB         PetStoreC          FoodStore
INSERT @CompanyEmployees VALUES (3,'Paul'  ,2     ,1)     --                      1-Jerome                 11-Alan           14-Ben              18-apple
INSERT @CompanyEmployees VALUES (4,'Jack'  ,3     ,1)     --                     /        \                /      \           /                  /     \
INSERT @CompanyEmployees VALUES (5,'Daniel',3     ,1)     --              2-Joe           9-Bill         12-Ally  13-Abby    15-Bill         19-pear   20-grape
INSERT @CompanyEmployees VALUES (6,'David' ,2     ,1)     --            /       \              \                             /      \                    /
INSERT @CompanyEmployees VALUES (7,'Ian'   ,6     ,1)     --     3-Paul          6-David       10-Sam                     16-Bjorn  17-Benny           21-rasin
INSERT @CompanyEmployees VALUES (8,'Helen' ,6     ,1)     --    /      \            /    \
INSERT @CompanyEmployees VALUES (9,'Bill ' ,1     ,1)     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @CompanyEmployees VALUES (10,'Sam'  ,9     ,1)     --
INSERT @CompanyEmployees VALUES (11,'Alan' ,NULL  ,2)     --to see all trees, scroll--->>
INSERT @CompanyEmployees VALUES (12,'Ally' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (13,'Abby' ,11    ,2)     --
INSERT @CompanyEmployees VALUES (14,'Ben'  ,NULL  ,3)     --     
INSERT @CompanyEmployees VALUES (15,'Bill' ,14    ,3)     --
INSERT @CompanyEmployees VALUES (16,'Bjorn',15    ,3)     --
INSERT @CompanyEmployees VALUES (17,'Benny',15    ,3)     --
INSERT @CompanyEmployees VALUES (18,'apple',NULL  ,6)     --
INSERT @CompanyEmployees VALUES (19,'pear' ,18    ,6)     --
INSERT @CompanyEmployees VALUES (20,'grape',18    ,6)     --
INSERT @CompanyEmployees VALUES (21,'rasin',21    ,6)     --
SET NOCOUNT OFF

;WITH StaffTree AS
(
    SELECT 
        c.id, c.name, c.reports_to_id, c.reports_to_id as Manager_id, cc.name AS Manager_name, 1 AS LevelOf, c.CompanyStoreID
        FROM @CompanyEmployees                c
            LEFT OUTER JOIN @CompanyEmployees cc ON c.reports_to_id=cc.id
        WHERE c.reports_to_id IS NULL
    UNION ALL
        SELECT 
            s.id, s.name, s.reports_to_id, t.id, t.name, t.LevelOf+1, s.CompanyStoreID
        FROM StaffTree                    t
            INNER JOIN @CompanyEmployees  s ON t.id=s.reports_to_id
)
SELECT
    c.id AS CompanyID, c.name AS CompanyName
        ,g.id AS CompanyGroupID, g.name AS CompanyName
        ,s.id AS CompanyStoreID, s.name AS CompanyStoreName
        ,t.id AS EmployeeID, t.name as EmployeeName, t.Manager_id, t.Manager_name, t.LevelOf
    FROM @Company                c
        LEFT JOIN @CompanyGroup  g ON c.id=g.CompanyID
        LEFT JOIN @CompanyStore  s ON g.id=s.CompanyGroupID
        LEFT JOIN StaffTree      t ON s.id=t.CompanyStoreID
    ORDER BY c.name,g.name,s.name,s.ID,t.LevelOf,t.name

OUTPUT:

CompanyID CompanyName    CompanyGroupID CompanyName CompanyStoreID CompanyStoreName EmployeeID  EmployeeName Manager_id  Manager_name LevelOf
--------- -------------- -------------- ----------- -------------- ---------------- ----------- ------------ ----------- ------------ -------
2         Boring Company 3              Electronics 6              FoodStore        18          apple        NULL        NULL         1
2         Boring Company 3              Electronics 6              FoodStore        20          grape        18          apple        2
2         Boring Company 3              Electronics 6              FoodStore        19          pear         18          apple        2
2         Boring Company 4              Food        NULL           NULL             NULL        NULL         NULL        NULL         NULL
1         Living Things  2              Humans      5              HumansStore      NULL        NULL         NULL        NULL         NULL
1         Living Things  1              Pets        1              PetsStoreA       1           Jerome       NULL        NULL         1
1         Living Things  1              Pets        1              PetsStoreA       9           Bill         1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       2           Joe          1           Jerome       2
1         Living Things  1              Pets        1              PetsStoreA       6           David        2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       3           Paul         2           Joe          3
1         Living Things  1              Pets        1              PetsStoreA       10          Sam          9           Bill         3
1         Living Things  1              Pets        1              PetsStoreA       5           Daniel       3           Paul         4
1         Living Things  1              Pets        1              PetsStoreA       8           Helen        6           David        4
1         Living Things  1              Pets        1              PetsStoreA       7           Ian          6           David        4
1         Living Things  1              Pets        1              PetsStoreA       4           Jack         3           Paul         4
1         Living Things  1              Pets        2              PetsStoreB       11          Alan         NULL        NULL         1
1         Living Things  1              Pets        2              PetsStoreB       13          Abby         11          Alan         2
1         Living Things  1              Pets        2              PetsStoreB       12          Ally         11          Alan         2
1         Living Things  1              Pets        3              PetsStoreC       14          Ben          NULL        NULL         1
1         Living Things  1              Pets        3              PetsStoreC       15          Bill         14          Ben          2
1         Living Things  1              Pets        3              PetsStoreC       17          Benny        15          Bill         3
1         Living Things  1              Pets        3              PetsStoreC       16          Bjorn        15          Bill         3
1         Living Things  1              Pets        4              PetsStoreD       NULL        NULL         NULL        NULL         NULL

(23 row(s) affected)

EDIT after OP's edit stating that We would like to see it in tree form.

The question is tagged sql-server-2008 and hierarchical-data, and the OP wants to do complex formatting for display f the data. However this type of processing and display is not the realm of TSQL and is a very clear example of where the application language should process and format flat data provided by a SQL query. I have provided such a query that could be used by an application to build a visual tree display. Also note that the simple tree example (no more than two children per parent) might not be very realistic and when many children exist for a single parent, the display will become difficult to construct and not pleasing to the eye.