Fetch previous columns when particular value appears in any column in the table

Question

Hi,

I have a Hierarchy table, which contains 5 levels of same details and i need to fetch the previous level of columns whenever the particular ID occurs in any of the level. Please find the table looks like below

DECLARE @Hierarchy TABLE (ID VARCHAR(10), L1ID VARCHAR(10), L1Name VARCHAR(30), L1UniqueId VARCHAR(10), L2ID VARCHAR(10), L2Name VARCHAR(30), L2UniqueId VARCHAR(10),L3ID VARCHAR(10), L3Name VARCHAR(30), L3UniqueId VARCHAR(10),L4ID VARCHAR(10), L4Name VARCHAR(30), L4UniqueId VARCHAR(10),L5ID VARCHAR(10), L5Name VARCHAR(30), L5UniqueId VARCHAR(10))  
  
INSERT INTO @Hierarchy VALUES ('AA11', 'BB22', 'BBB','BB22','CC33', 'CCC','CC33','DD44', 'DDD','DD44','AA11', 'AAA','AA11', 'BLANK', '', '')  
INSERT INTO @Hierarchy VALUES ('XX21', 'MM66', 'MMM','MM66','NN77', 'NNN','NN77','OO88', 'OOO','OO88','PP99', 'PPP','PP99', 'XX21', 'XXX', 'XX21')  
INSERT INTO @Hierarchy VALUES ('YY32', 'SS41', 'SSS','SS41','TT51', 'TTT','TT51','YY32', 'YYY','YY32','BLANK', '','', 'BLANK', '', '')  
  
SELECT * FROM @Hierarchy  

For ex

Here in the above example, i am looking for ID column (1st col) value that must appear anywhere in the table and whenever the same ID appears at any level, i need to fetch the previous level as my output.

Result expected:

Answer 1

Hi @Fazlu AM ,

Welcome to Microsoft Q&A!

Please refer below:

SELECT ID,  
CASE WHEN ID=L5ID THEN L4ID WHEN ID=L4ID THEN L3ID WHEN ID=L3ID THEN L2ID WHEN ID=L2ID THEN L1ID END PREVID,  
CASE WHEN ID=L5ID THEN L4Name WHEN ID=L4ID THEN L3Name WHEN ID=L3ID THEN L2Name WHEN ID=L2ID THEN L1Name END PREVNAME,  
CASE WHEN ID=L5ID THEN L4UniqueId WHEN ID=L4ID THEN L3UniqueId WHEN ID=L3ID THEN L2UniqueId WHEN ID=L2ID THEN L1UniqueId END PREUniqueId  
FROM @Hierarchy  

OR

;with cte as (  
SELECT  
t.ID, substring(x.col,2,1) level,x.col, x.value  
FROM @Hierarchy t  
CROSS APPLY(VALUES  
('L1IDD', t.L1ID),  
('L1Name', t.L1Name),  
('L1UniqueId', t.L1UniqueId),  
('L2IDD', t.L2ID),  
('L2Name', t.L2Name),  
('L2UniqueId', t.L2UniqueId),  
('L3IDD', t.L3ID),  
('L3Name', t.L3Name),  
('L3UniqueId', t.L3UniqueId),  
('L4IDD', t.L4ID),  
('L4Name', t.L4Name),  
('L4UniqueId', t.L4UniqueId),  
('L5IDD', t.L5ID),  
('L5Name', t.L5Name),  
('L5UniqueId', t.L5UniqueId)  
)x(col, value))  
,cte1 as (  
select a.id,SUBSTRING(a.col,3,len(a.col)-2) col,a.value from cte a  
inner join cte b  
on a.level=b.level-1 and a.id=b.value)  
select ID,[IDD] PREVID,[NAME] PREVNAME,[UniqueId] PREUniqueId  from   
(select id,col,value from cte1) s  
pivot  
(max(value) for col IN ([IDD],[NAME],[UniqueId])) P  

Output:

ID PREVID PREVNAME PREUniqueId  
AA11 DD44 DDD DD44  
XX21 PP99 PPP PP99  
YY32 TT51 TTT TT51  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.