Hierarchy chain - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Hierarchy chain

Author

Topic

sureshmoorthy
Starting Member

1 Post

Posted - 2014-02-07 : 11:52:07

Dear all..

I need to built the hierarchy chain on parts. below is the sample data and expected result from this.

Sample data:
OLD_PART_NO NEW_PART_NO
A B
B C
C 1
D C
E C
F G
G I
I 1
J I
K I
L I

Expected result for one part '1':
PART_NO OLD_PART_NO NEW_PART_NO
1 A B
1 B C
1 D C
1 E C
1 C 1
1 F G
1 G I
1 J I
1 K I
1 L I
1 I 1

Please guide me how to do this.

Thanks All

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 01:30:29

use a CTE
like below


;With PartChain
AS
(
SELECT OLD_PART_NO,NEW_PART_NO, 1 AS Level,CAST(OLD_PART_NO AS varchar(max)) AS [Path]
FROM Table
WHERE NEW_PART_NO = @PartNo
UNION ALL
SELECT t.*,c.Level + 1,CAST(c.Path + '/' + t.OLD_PART_NO AS varchar(max))
FROM PartChain c
INNER JOIN Table t
ON t.NEW_PART_NO = c.OLD_PART_NO
)
SELECT *
FROM PartChain
ORDER BY LEFT([Path],CHARINDEX('/',Path + '/')-1),Level DESC
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources