SQL group with Recursive CTE

Question

I'm working on SQL Server 2008. I believe the answer to my Q lies in a recursive CTE but any solution would be greatly appreciated.

In the sam_DB.dbo.example table below where the PID is not null it links back to an ID

    ID     | PID   
    ------ | ------
    1      | NULL  
    2      | 1     
    3      | 2     
    4      | 3     
    5      | NULL  
    6      | 5     
    7      | 6     
    8      | NULL  
    9      | NULL  
    10     | 9     

I want my output to have a new field (CID) that identifies each record in a chain of linkages from PID to ID as part of a group, as per below.

   ID     | PID    | CID   
   ------ | ------ | ------
   1      | NULL   | 1     
   2      | 1      | 1     
   3      | 2      | 1     
   4      | 3      | 1     
   5      | NULL   | 2     
   6      | 5      | 2     
   7      | 6      | 2     
   8      | NULL   | 3     
   9      | NULL   | 4     
   10     | 9      | 4     

Answer 1

you have to use Common table expression With ROW_NUMBER() Window function

CREATE TABLE #TblTemp(ID int,PID int);
INSERT INTO #TblTemp(ID ,PID ) VALUES (1,NULL),(2,1),(3,1),(4,3),(5,NULL),(6,5),(7,6),(8,NULL),(9,NULL),(10,9);
WITH CTE (ID, PID, CID) AS (
    SELECT ID, PID, ROW_NUMBER() OVER(ORDER BY ID) RN
    FROM #TBLTEMP
    WHERE PID IS NULL
UNION ALL
    SELECT T.ID, T.PID, C.CID 
    FROM CTE C
    INNER JOIN #TBLTEMP T
    ON T.PID = C.ID
);
SELECT * 
FROM CTE 
ORDER BY ID;