April 11, 2018 at 6:42 am
Hello All, I have one t-sql related question. I have one table which contains hierarchy data in following format:
Id PersonId PersonName HierarchyId
1 1001 'Benjamin' '0'
2 1002 'Andy' '0:1001'
3 1003 'Randolph' '0:1001'
4 1004 'Megha' '0:1001:1002'
5 1005 'Ray' '0:1001:1002'
6 1006 'Altaf' '0:1001:1003'
... and so on.
I want this table's data in following format:
ID PersonId PersonName ID1 PersonId1 PersonName1 ID2 PersonId2 PersonName2 ID3 PersonId3 PersonName3
1 1001 'Benjamin' NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 1002 'Andy' 1 1001 'BENJAMIN' NULL NULL NULL NULL NULL NULL
3 1003 'Randolph' 1 1001 'BENJAMIN' NULL NULL NULL NULL NULL NULL
4 1004 'Megha' 1 1001 'BENJAMIN' 2 1002 'Andy' NULL NULL NULL
5 1005 'Ray' 1 1001 'BENJAMIN' 2 1002 'Andy' NULL NULL NULL
6 1006 'Altaf' 1 1001 'BENJAMIN' 3 1003 'Randolph' NULL NULL NULL
... and so on.
April 11, 2018 at 7:12 am
The function DelimitedSplit8k is here.
;WITH MyTable AS (
SELECT * FROM ( VALUES
(1, 1001, 'Benjamin', '0'),
(2, 1002, 'Andy', '0:1001'),
(3, 1003, 'Randolph', '0:1001'),
(4, 1004, 'Megha', '0:1001:1002'),
(5, 1005, 'Ray', '0:1001:1002'),
(6, 1006, 'Altaf', '0:1001:1003')
) d (Id, PersonId, PersonName, HierarchyId)
)
SELECT m.Id, m.PersonId, m.PersonName, x.*
FROM MyTable m
OUTER APPLY (
SELECT
ID1 = MAX(CASE WHEN ds.ItemNumber = 2 THEN mi.Id END),
PersonId1 = MAX(CASE WHEN ds.ItemNumber = 2 THEN mi.PersonId END),
PersonName1 = MAX(CASE WHEN ds.ItemNumber = 2 THEN mi.PersonName END),
ID2 = MAX(CASE WHEN ds.ItemNumber = 3 THEN mi.Id END),
PersonId2 = MAX(CASE WHEN ds.ItemNumber = 3 THEN mi.PersonId END),
PersonName2 = MAX(CASE WHEN ds.ItemNumber = 3 THEN mi.PersonName END),
ID3 = MAX(CASE WHEN ds.ItemNumber = 4 THEN mi.Id END),
PersonId3 = MAX(CASE WHEN ds.ItemNumber = 4 THEN mi.PersonId END),
PersonName3 = MAX(CASE WHEN ds.ItemNumber = 4 THEN mi.PersonName END)
FROM [dbo].[DelimitedSplit8K] (m.[HierarchyId], ':') ds
INNER JOIN MyTable mi ON mi.PersonId = ds.Item
) x
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
May 2, 2018 at 1:20 pm
It was awesome Chris and apologies for not able to reply back as I was on vacation. The code works like charm. Though I have one more connected issue. Please see if you can give any work around on this please.
I have this one hierarchy of few specific type of people who contains high level management membership plus few high level employees. Also there is another hierarchy which contains only employees. The first hierarchy contains employees at last level. In the second hierarchy those employees (from first hierarchy may be present at any level). I need a flattened hierarchy out of the two based on first hierarchy.
Taking same example as before, here is my first hierarchy
Id PersonId PersonName HierarchyId
1 1001 'Benjamin' '0'
2 1002 'Andy' '0:1001'
3 1003 'Randolph' '0:1001'
4 1004 'Megha' '0:1001:1002'
5 1005 'Ray' '0:1001:1002'
6 1006 'Altaf' '0:1001:1003
And here is my second hierarchy
Id PersonId PersonName HierarchyId
1 5001 'Ben' '0'
2 5002 'Manas' '0:5001'
3 5003 'Liam' '0:5001'
4 1004 'Megha' '0:5001:5002'
5 1005 'Ray' '0:5001:5002'
6 1008 'Benjamin' '0:5001:5003'
7 5025 'Avesh' '0:5001:5002:1004'
8 5030 'Short' '0:5001:5002:1004:5025'
9 5032 'Glenn' '0:5001:5002:1004:5025'
10 5033 'Bill' '0:5001:5002:1008'
11 1006 'Altaf' '0:5001:5002:1008'
12 5038 'Devi' '0:5001:5002:1008:1006'
And i want a collective result as follows:
PersonId PersonName ID1 PersonId1 PersonName1 ID2 PersonId2 PersonName2 ID3 PersonId3 PersonName3
1 1001 'Benjamin' NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 1002 'Andy' 1 1001 'BENJAMIN' NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 1003 'Randolph' 1 1001 'BENJAMIN' NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 1004 'Megha' 1 1001 'BENJAMIN' 2 1002 'Andy' NULL NULL NULL NULL NULL NULL
5 1005 'Ray' 1 1001 'BENJAMIN' 2 1002 'Andy' NULL NULL NULL NULL NULL NULL
6 1006 'Altaf' 1 1001 'BENJAMIN' 3 1003 'Randolph' NULL NULL NULL NULL NULL NULL
7 5025 'Avesh' 1 1001 'BENJAMIN' 2 1002 'Andy' 4 1004 'Megha' NULL NULL NULL
8 5030 'Short' 1 1001 'BENJAMIN' 2 1002 'Andy' 4 1004 'Megha' 7 5025 'Avesh'
9 5032 'Glenn' 1 1001 'BENJAMIN' 2 1002 'Andy' 4 1004 'Megha' 7 5025 'Avesh'
10 5038 'Devi' 1 1001 'BENJAMIN' 3 1003 'Randolph' 6 1006 'Altaf' NULL NULL NULL
So basically overall it's like:
a. Get the hierarchy flattening from first hierarchy
b. The leaf level of the first flattened Hierarchy will be in second Hierarchy.
c. Continue the Hierarchy flattening from that leaf level onwards from second Hierarchy.
Sounds crazy I know.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply