Reputation: 58522
SQL Server Query or Tool to show Hierarchical Data
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
)
Upvotes: 5
Views: 5994
Answers (4)
Reputation: 103587
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.
Upvotes: 2
Reputation: 4509
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.
Upvotes: 0
Reputation: 13702
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
Upvotes: 0
Related Questions
- SQL query to find multi level hierarchical data
- Multi-Table Hierarchy Query
- Get hierarchical structure using SQL Server
- SQL Server: querying hierarchical and referenced data
- SQL SERVER 2008 tree query
- SQL Server Tree Query
- How to create views against a hierarchical table
- How to query hierarchical information with SQL-Server 2000?
- Determine the hierarchy of records in a SQL database
- Hierarchical query in SQL Server 2005