Sql-server – How to get the hierarchical values from the below query

ctesql-server-2012subqueryt-sql

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.

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 NULL for 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)

Best Answer

I was able to replicate your results with your example data.

Your issue is that in the "base" case of the recursive CTE (the first select statement) you are getting the records where fParentCategoryID is null and casting the fParentCategoryID to a character column ready to append the "children". However the 'string' is still a NULL value at this stage (try it with SELECT CAST(NULL AS VARCHAR(200)) or such like).

The second select statement then tries to append other string values to the NULL but by default, the database option "concat null yields null" says if you try to concatenate anything to a NULL value then you still just get NULL - rather than treat it as an empty string.

You could SET CONCAT_NULL_YIELDS_NULL OFF for this query although this is deprecated and will ultimately be removed in a future version of SQL Server (so you probably shouldn't be adding it to production code!)

A better way is in the first select statement, instead of casting the fParentCategoryID to a string, just cast the CategoryID - we already know the parent should be empty as they are the NULLs.

SELECT c.CategoryID,
   c.fParentCategoryID,
   [level] = 1,
   ParChild=cast(CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX))
FROM   retail.Category c
WHERE c.fParentCategoryID is NULL

If your data could have a mixture of null and non null values in that select statement, you could also use ISNULL around it to treat any nulls as empty strings.

It probably needs some cleanup on the ParChild value it produces as you get values like " ,461,464" so you might want to strip the beginning comma off.

Related Question