Anyone can help me to come out a query for below ? The objective is to transform entity as shown in expected output.
Child Parent Name
0001 0001 HQ
0100 0001 HQ Accounting Dept
0200 0001 HQ Marketing Dept
0300 0001 HQ HR Dept
0101 0100 Branch North 111
0102 0100 Branch North 112
0201 0200 Branch North 113
0301 0300 Branch North 114
8900 0300 Branch North 115
0387 8900 Sub Branch North 115
Expected output
----------------
Level1 Level2 Level3 Level4 Name
0001 0100 0101 N/A Branch North 111
0001 0100 0102 N/A Branch North 112
0001 0200 0201 N/A Branch North 113
0001 0300 0301 N/A Branch North 114
0001 0300 8900 0387 Sub Branch North 115
I've tried to query it but answer is not really correct
with cte as
(select Child,Parent from cmbc_entity
where Parent = '0001'),
cte2 as
(select A.Parent as Level1, B.Child as Level2, C.Child as Level3, C.Name
from cmbc_entity B inner join cte A on A.Child = B.Parent inner join cmbc_entity C on B.Child = C.Parent
where B.Child != '0001')
select * from cte2
The limitation with pulling the data set this way is that you will of course never be able to hit more than 4 levels. Dynamic columns is a major pain to try and implement.
You could probably set up a recursive ID, but your column would be more like Self, Parent, Grandparent, grandgrandparent, name which could get a little weird quickly.
CREATE TABLE Department (
DeptID INT,
ParentID INT,
Name VARCHAR(255));
INSERT INTO Department
VALUES
(1,1,'HQ'),
(100,1,'HQ Accounting Dept'),
(200,1,'HQ Marketing Dept'),
(300,1,'HQ HR Dept'),
(101,100,'Branch North 111'),
(102,100,'Branch North 112'),
(201,200,'Branch North 113'),
(301,300,'Branch North 114'),
(8900,300,'Branch North 115'),
(387,8900,'Sub Branch North 115');
;WITH cte_Level1 AS (
SELECT
CONVERT(VARCHAR(50), DeptID) [Level1],
CONVERT(VARCHAR(50), 'N/A') [Level2],
CONVERT(VARCHAR(50), 'N/A') [Level3],
CONVERT(VARCHAR(50), 'N/A') [Level4],
Name
FROM Department
WHERE DeptID = ParentID
), cte_Level2 AS (
SELECT * FROM cte_Level1 UNION ALL
SELECT
CONVERT(VARCHAR(50), A.Level1) [Level1],
CONVERT(VARCHAR(50), D.DeptID) [Level2],
CONVERT(VARCHAR(50), 'N/A') [Level3],
CONVERT(VARCHAR(50), 'N/A') [Level4],
D.Name
FROM Department D
INNER JOIN cte_Level1 A ON A.Level1 = D.ParentID
WHERE D.DeptID <> D.ParentID
), cte_Level3 AS (
SELECT * FROM cte_Level2 UNION ALL
SELECT
CONVERT(VARCHAR(50), A.Level1) [Level1],
CONVERT(VARCHAR(50), A.Level2) [Level2],
CONVERT(VARCHAR(50), D.DeptID) [Level3],
CONVERT(VARCHAR(50), 'N/A') [Level4],
D.Name
FROM Department D
INNER JOIN cte_Level2 A ON A.Level2 = D.ParentID
AND A.Level2 <> 'N/A'
WHERE D.DeptID <> D.ParentID
), cte_Level4 AS (
SELECT * FROM cte_Level3 UNION ALL
SELECT
CONVERT(VARCHAR(50), A.Level1) [Level1],
CONVERT(VARCHAR(50), A.Level2) [Level2],
CONVERT(VARCHAR(50), A.Level3) [Level3],
CONVERT(VARCHAR(50), D.DeptID) [Level4],
D.Name
FROM Department D
INNER JOIN cte_Level3 A ON A.Level3 = D.ParentID
AND A.Level3 <> 'N/A'
WHERE D.DeptID <> D.ParentID
)
SELECT * FROM cte_Level4
How about using this elegant approach to flatten your hierarchy, using recursive CTE's.
Here's a solution to a similar question
Stack Overflow: Flattening Hierarchy series using SQL Server CTE
Test my script with other sample data.Though i am myself not much satisfied because i hate using distinct.
I will try to modify and update here for following reason :-
- Why I have to use distinct?
- why i am using b.level1 is not null in my final query .
At the same time i am quite sure about other thing .Like my script is very short.Also it is easy to convert to dynamic sql because of levelid.
are you sure about your output ?
Declare @t table (Child varchar(10), Parent varchar(10), Name varchar(50))
insert into @t
select '0001','0001','HQ' union all
select'0100','0001','HQ Accounting Dept' union all
select'0200','0001','HQ Marketing Dept' union all
select'0300','0001','HQ HR Dept' union all
select'0101','0100','Branch North 111' union all
select'0102','0100','Branch North 112'union all
select'0201','0200','Branch North 113'union all
select'0301','0300','Branch North 114'union all
select'8900','0300','Branch North 115'union all
select '0387','8900','Sub Branch North 115'
--select * from @t
;with CTE as
(
select child,parent,name,case when parent=child then 1 else 0 end level1 from @t a
)
,cte2 as
(
select * from cte where level1=1
union all
select a.Child,a.Parent,a.Name,b.level1+1 from @t a inner join cte2 b on a.parent=b.child and a.child<>b.child
)
select distinct
isnull(b.child,'N/A') Level1
,isnull(c.child,'N/A') Level2
,isnull(d.child,'N/A') Level3
,isnull(e.child,'N/A') Level4
,case when e.child is null then d.name
when d.child is null then c.name
when c.child is null then b.name
when b.child is null then a.name else e.Name end Name
from cte2 a
left join cte2 b on a.parent=b.child and b.level1=1
left join cte2 c on b.child=c.parent and c.level1=2
left join cte2 d on c.Child=d.Parent and d.level1=3
left join cte2 e on d.Child=e.Parent and e.level1=4
where b.level1 is not null
