I have a table named
Category which has a column named
CategoryID. There is a referencing column in the same table called
I need to get all the category IDs and their subcategory IDs comma separated. For Example - if parent category ID of 10 is 1 and if parent category Id of 20 is 10 then when I print category ID 20 I need to print both 1 and 10 as its parents in comma separated values.
I tried the below query but I get
ParChild column. Please help.
;WITH cteReports AS ( SELECT c.CategoryID, c.fParentCategoryID, [level] = 1, ParChild=cast(CAST(c.fParentCategoryID AS VARCHAR(200)) + ',' + CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX)) FROM retail.Category c WHERE c.fParentCategoryID is NULL UNION ALL SELECT c.CategoryID, c.fParentCategoryID, [level] + 1, ParChild = ParChild + ',' + CAST(c.CategoryID AS VARCHAR(200)) FROM retail.Category c JOIN cteReports r ON c.fParentCategoryID = r.CategoryID ) SELECT * FROM cteReports cr
use this script to create and populate the table. (note: there is a 30K limit to the question body..So I had to use pastebin to copy your code and reference it)