SQLTeam.com | Weblogs | Forums

How To Get Full Code used ParentID


#1

I have Table (ID , Name , ParentID)
ID Name ParentID
1 s null
2 a 1
3 w 2
4 e 2
5 r null
6 l 5
7 b 6
8 k 6
I need Result
ID Name ParentID FullCode
1 s null 01
2 a 1 0101
3 w 2 010101
4 e 2 010102
5 r null 02
6 l 5 0201
7 b 6 020101
8 k 6 020102

I Need Query To Get FullCode


#2
-- *** Consumable Test Data ***
-- Please provide in future.
CREATE TABLE #t
(
    ID int NOT NULL PRIMARY KEY
    ,Name varchar(20) NOT NULL
    ,ParentID int NULL
);
INSERT INTO #t
VALUES (1, 's', NULL),(2, 'a', 1),(3, 'w', 2),(4, 'e', 2)
    ,(5, 'r', NULL),(6, 'l', 5),(7, 'b', 6),(8, 'k', 6);
-- *** End Test Data ***

WITH Hierarchy
AS
(
    SELECT ID, Name, ParentID
        ,CAST(RIGHT('0' + CAST(ROW_NUMBER() OVER (ORDER BY ID) AS varchar(2)), 2) AS varchar(20)) AS FullCode
    FROM #t
    WHERE ParentID IS NULL

    UNION ALL

    SELECT T.ID, T.Name, T.ParentID
        ,CAST(H.FullCode
        + RIGHT('0' + CAST(ROW_NUMBER() OVER (PARTITION BY T.ParentID ORDER BY T.ID) AS varchar(2)), 2)
         AS varchar(20))
    FROM #t T
        JOIN Hierarchy H
            ON T.ParentID = H.ID

)
SELECT *
FROM Hierarchy
ORDER BY ID;