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.

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.