How can I get the hierarchical values from the below query?
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.