Sql query to Get all Parent Id in sql server

I hope you are enjoy my article on this blog. I am continuously sharing good article that helps developers a lot.Today i am going to show how to get parent Id using recursive function in SQL Server. Using recursive query you will call same datatable. I have shared real time example  of my project and try my hand on recursive query first time.

Problem
I have table which have column that self reference table Id. Now  I want to fetch all parentId list of each with comma separator. After googling this problem i found some good queries which i am going to share in this article.

Example
I have created table productcategory having fields Id, Name and ParentId. ParentId helps to indicate parent category Id of same table.
CREATE TABLE ProductCategory(
 Id int IDENTITY(1,1) NOT NULL,
 Name varchar(50) NOT NULL,
 ParentId int NOT NULL)
I have added same data so we can execute query
how-to-get-parent-id-using-recursive-query
Query
WITH is common Table expression(CTE) that will derive from simple query and it can include references to itself. You can learn more from msdn library. We must include where conditions on first select statement to get distinct result.
;WITH Recursives AS (
        SELECT  *,
                CAST(Id AS VARCHAR(MAX)) + ',' ParentID_List
        FROM    ProductCategory pc
        WHERE   Pc.ParentId = 0
        UNION ALL
        SELECT  t.*,
        r.ParentID_List + CAST(t.Id AS VARCHAR(MAX)) + ','
        FROM    ProductCategory t INNER JOIN
                Recursives r    ON  t.ParentId = r.Id
    
)
SELECT  * FROM  Recursives 
Result:
after running query given above we will get all ParentID list.
how-to-get-all-parent-id-using-recursive-query