Menu
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
)
580
A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures. In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions (CTEs).
Use hierarchyid as a data type to create tables with a hierarchical structure, or to describe the hierarchical structure of data that is stored in another location. Use the hierarchyid functions in Transact-SQL to query and manage hierarchical data.
Representing hierarchical information is quite straightforward; in the majority of cases the information designer will either use a tree diagram (or a variant of a tree diagram) or a treemap to display the data in an efficient format for the user.
Hierarchical query is a type of SQL query that is commonly leveraged to produce meaningful results from hierarchical data. Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships.
Brad Schulz to the rescue in the form of usp_DrawTree.
102
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.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
