SQL Server Category List SUBSTRING

Question

I want to show categories in one line, let's give an example like this:

Category for a product iPhone 6 -> Apple -> Mobile Phones -> Electronics

I just want to show the current category of the product + a top category + the top category, so:

Electronic / ... / Apple / iPhone 6, I want to show the ... between categories that I don't want to show.

Here is my code

WITH cte AS (
    SELECT a1.*
    FROM dbo.viewFeatureProductCategory a1
    WHERE a1.Id = 1000184
    UNION ALL
    SELECT a2.*
    FROM dbo.viewFeatureProductCategory a2
    join cte c on a2.Id = c.ParentId
) SELECT TOP 1 SUBSTRING((SELECT ' / ' + SPACE (1) + Name as 'data()' FROM cte order by ParentId for XML PATH('')), 4, 99999) from cte

and here is result of the code

it's better to post a sample of input table and the expected output. — ahmed, Jul 23 '22 at 16:06
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Providing DDL and sample data as text helps us help you. Images, not so much. — HABO, Jul 23 '22 at 17:58

score 0 · Answer 1 · answered Jul 24 '22 at 02:38

You just need to keep track of the previous Name value, as well as the starting Name value. Then filter the CTE to the top level row and concatenate the values together.

CONCAT_WS concatenates values with a separator.

WITH cte AS (
    SELECT a1.ParentId, a1.Name, PreviousName = NULL, OriginalName = a1.Name
    FROM dbo.viewFeatureProductCategory a1
    WHERE a1.Id = 1000184

    UNION ALL

    SELECT a2.ParentId, a2.Name, PreviousName = c.Name, OriginalName = c.OriginalName 
    FROM dbo.viewFeatureProductCategory a2
    JOIN cte c on a2.Id = c.ParentId
)
SELECT CONCAT_WS(' / ', cte.Name, '...', cte.PreviousName, cte.OriginalName)
FROM cte
WHERE cte.ParentId IS NULL;

Alternatively, if you only want the bottom two levels, you can do this

WITH cte AS (
    SELECT a1.ParentId, a1.Name, Name2 = NULL, OriginalName = a1.Name
    FROM dbo.viewFeatureProductCategory a1
    WHERE a1.Id = 1000184

    UNION ALL

    SELECT a2.ParentId, a2.Name, ISNULL(c.Name2, c.Name), c.OriginalName 
    FROM dbo.viewFeatureProductCategory a2
    JOIN cte c on a2.Id = c.ParentId
)
SELECT CONCAT_WS(' / ', cte.OriginalName, cte.Name2, '...', cte.Name)
FROM cte
WHERE cte.ParentId IS NULL;

SQL Server Category List SUBSTRING

1 Answers1