CODEWITHSUNDEEP
sqlsql-server-2008parent-childcommon-table-expression
Brijesh Patel
Brijesh Patel

Reputation: 2958

Get records containing all the child records in sql server

I have heirarchical data in sql server. Follwing are my data heirarcy tree.

Id      Name         ParentID

1124    ABC          2
1125    BCD          1124
1126    EFG          1124   
1127    HIJ          1126   
1128    KLM          1126
1129    OPQ          1124   
1130    RST          1124
1131    UVW          1130
1132    XYZ          1131
1133    ZYA1         1124

Now i want following result from above table. In that i want path from root (parent) with all child till last child. 

Id      Name    ParentID    PATH

1124    ABC     2           ABC
1125    BCD     1124        ABC/BCD
1126    EFG     1124        ABC/EFG
1127    HIJ     1126        ABC/EFG/HIJ
1128    KLM     1126        ABC/EFG/KLM
1129    OPQ     1124        ABC/OPQ
1130    RST     1124        ABC/RST
1131    UVW     1130        ABC/RST/UVW
1132    XYZ     1131        ABC/RST/UVW/XYZ
1133    ZYA1    1124        ABC/ZYA1

So can anyone help me to write the sql query to find out the path which containing all the records from parent to its all containing child.

Thanks in advance.

Upvotes: 1

Views: 3402

Answers (2)

GarethD
GarethD

Reputation: 69759

You need to use a recursive common table expression, then filter the results for only the base path for each record, (i.e. for ID 1131 get ABC/RST/UVW and not just ABC/RST

WITH CTE AS
(   SELECT  ID, 
            Name, 
            ParentID, 
            NextParentID = ParentID, 
            Path = CAST(Name AS VARCHAR(MAX)),
            Recursion = 1
    FROM    T
    UNION ALL
    SELECT  CTE.ID, 
            CTE.Name, 
            CTE.ParentID, 
            T.ParentID, 
            CAST(T.Name + '/' + CTE.Path AS VARCHAR(MAX)),
            Recursion + 1
    FROM    CTE
            INNER JOIN T
                ON CTE.NextParentID = T.ID
), CTE2 AS
(   SELECT  CTE.ID,
            CTE.Name,
            CTE.ParentID,
            CTE.Path,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY CTE.ID ORDER BY Recursion DESC)
    FROM    CTE
)
SELECT  ID, Name, ParentID, Path
FROM    CTE2
WHERE   RowNumber = 1;

Example on SQL Fiddle

Upvotes: 3

TechDo
TechDo

Reputation: 18629

Please try:

DECLARE @table as TABLE(Id INT, [Name] NVARCHAR(50), ParentID INT)

insert into @table values
(1124, 'ABC', 2),
(1125, 'BCD', 1124),
(1126, 'EFG', 1124),
(1127, 'HIJ', 1126), 
(1128, 'KLM', 1126),
(1129, 'OPQ', 1124), 
(1130, 'RST', 1124),
(1131, 'UVW', 1130),
(1132, 'XYZ', 1131),
(1133, 'ZYA1', 1124)

;WITH parent AS
(
    SELECT *, convert(nvarchar(max), [Name]) as [Path] from @table WHERE ParentID = 2
    UNION ALL 
    SELECT t.*, convert(nvarchar(max), parent.[Path]+'\'+t.[Name]) as [Path]
    FROM parent
    INNER JOIN @table t ON t.parentid =  parent.id
)SELECT * FROM parent
ORDER BY ID

Upvotes: 0

Related Questions