November 25, 2013 at 3:21 am
Hi,
I have a Employee, Manager, country hierarchy.
Table structure as below.
dbo.Entity
EntityId EntityTypeId Description ParentEntityId
101 1 UK NULL
102 1 USA NULL
103 2 SouthEast 101
104 2 SouthWest 101
105 3 Manager1 103
106 4 Employee1 105
107 4 Employee2 105
dbo.EntityType
1 Country
2 CountryRegion
3 Manager
4 Employee
I would like to write a query to produce resultset at employee level to top level(country)
For example,
106 Employee1 Manager1 SouthEast UK
I am stucked how to write this query. Can anyone suggest?
Thanks
November 25, 2013 at 4:41 am
Hi,
Imho you can do that in this way.
create table dbo.Entity (EntityId int, EntityTypeId smallint, Description varchar(25), ParentEntityId int);
insert into Entity
select 101,1,'UK',NULL
union all
select 102,1,'USA',NULL
union all
select 103,2,'SouthEast',101
union all
select 104,2,'SouthWest',101
union all
select 105,3,'Manager1',103
union all
select 106,4,'Employee1',105
union all
select 107,4,'Employee2',105
;WITH RecursLvl0 (Id,CountryRegion,Country)
AS
( SELECT b.EntityId,a.Description,b.Description
FROM dbo.Entity as a INNER JOIN dbo.Entity as b
ON b.ParentEntityId = a.EntityId WHERE a.EntityTypeId =1 AND b.EntityTypeId =2 )
,
RecursLvl1 (Id,Manager,CountryRegion,Country)
AS ( SELECT a.EntityId,a.Description,b.CountryRegion,b.Country
FROM dbo.Entity as a INNER JOIN RecursLvl0 as b
ON b.Id = a.ParentEntityId WHERE a.EntityTypeId =3
)
SELECT a.EntityId,a.Description,b.Manager,b.CountryRegion,b.Country
FROM dbo.Entity as a INNER JOIN RecursLvl1 as b
ON b.Id = a.ParentEntityId WHERE a.EntityTypeId =4;
drop table dbo.Entity;
Regards
Mike
November 25, 2013 at 7:10 am
If your hierarchy is likely to remain consistent then this may be more efficient than a rCTE:
DROP TABLE #Entity
CREATE TABLE #Entity (EntityId INT, EntityTypeId INT, Description VARCHAR(100), ParentEntityId INT)
INSERT INTO #Entity (EntityId, EntityTypeId, Description, ParentEntityId)
SELECT 101, 1, 'UK', NULL UNION ALL
SELECT 102, 1, 'USA', NULL UNION ALL
SELECT 103, 2, 'SouthEast', 101 UNION ALL
SELECT 104, 2, 'SouthWest', 101 UNION ALL
SELECT 105, 3, 'Manager1', 103 UNION ALL
SELECT 106, 4, 'Employee1', 105 UNION ALL
SELECT 107, 4, 'Employee2', 105
DROP TABLE #EntityType
CREATE TABLE #EntityType (EntityTypeId INT, ETdescription VARCHAR(100))
INSERT INTO #EntityType (EntityTypeId, ETdescription)
SELECT 1, 'Country' UNION ALL
SELECT 2, 'CountryRegion' UNION ALL
SELECT 3, 'Manager' UNION ALL
SELECT 4, 'Employee'
;WITH DenormalisedData AS (
SELECT e.EntityId, e.EntityTypeId, et.ETdescription, e.Description, e.ParentEntityId
FROM #Entity e
INNER JOIN #EntityType et ON et.EntityTypeId = e.EntityTypeId
)
SELECT
[EmployeeID] = l4.EntityId,
[Employee] = l4.Description,
[Manager] = l3.Description,
[Region] = l2.Description,
[Country] = l1.Description
FROM DenormalisedData l4
LEFT JOIN DenormalisedData l3 ON l3.EntityId = l4.ParentEntityId
LEFT JOIN DenormalisedData l2 ON l2.EntityId = l3.ParentEntityId
LEFT JOIN DenormalisedData l1 ON l1.EntityId = l2.ParentEntityId
WHERE l4.EntityTypeId = 4
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 25, 2013 at 8:13 am
Hi Friends,
Thanks for the replies. Both solutions worked for me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply