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

You could use reporting services to display it back which you get with SQL 2008; if you are lucky it might be setup already -- if not its quite easy to do that. You could use the drill in features in reporting services to get allow your users to drill in and out of the data as needed very easily.

In terms of the query; does the tree grow or is it fixed? The SQL Query to get the data out from the database is quite simple though.

Select 
    CompanyName,
    CompanyGroupName,
    CompanyStoreName,
    CompanyEmployeeForename,
    CompanyEmployeeSurname

From tblCompanies com
left outer join tblCompanyGroups cg
on com.CompanyGroupID = cg.CompanyGroupID

Left outer Join tblCompanyStore cs
on com.CompanyID = cs.CompanyID

left outer join tblCompanyEmployees ce
on com.CompanyID = ce.CompanyName

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.

Answer 3

I believe SQL Server 2008 offers a new data type to help with this scenario. Here is a link that I believe will help - http://msdn.microsoft.com/en-us/magazine/cc794278.aspx. I didn't see it in any comments, so hope this helps.

Answer 4

Brad Schulz to the rescue in the form of usp_DrawTree.