SQL Server query with treelist child names in one column and join table

Question

I have 3 tables:

Article:

Id, Name, Gropu, Measure, Price
----------------------------------
1, Coffee, 2, 1, 2.95
2, Tea, 2, 1, 1.95
3, CocaCola, 1, 1, 2,95

Measure:

Id, Name
---------
1, Cup

Group:

Id, ParentId, Name
----------------------
1, null, Beverages
2, 1, Hot beverages

So idea is to join all tables and get data like on table below

Article.Id, Article.Name, Article.Group, Group.Name, Article.Measure, Measure.Name, Article.Price
-----------------------------------------------------------------------------------------------------
1, Coffee, 2, Beverages - Hot beverages, 1, Cup, 2.95
2, Tea, 2, Beverages - Hot beverages, 1, Cup, 1.95
3, CocaCola, 1, Beverages, 1, Cup, 2.95 

All what I need is that I got in same column all tree list hierarchy names from top node to last one for article (in this example its only 2 levels but in reality can be infinite levels)

So I need in one column all names of hierarchy names with something between names like ' - ' from one article.

I hope someone got fast query because it has thousands of articles in the real database

Answer 1

First make sure there are no loops in your Group table hierarchy, and if your Group table has a lot of records, then make sure the table has an index on Id (including Name) and another index on the ParentId (also including Name).

Then something like this (can't actually test without the table definitions) should do the trick:

;WITH GroupHierarchy AS
(
    -- Anchor
    SELECT
        GroupId = G.Id,
        ParentId = G.ParentID,
        GroupName = G.Name,
        HierarchyName = CONVERT(VARCHAR(MAX), G.Name),
        Level = 1
    FROM
        [Group] AS G
    WHERE
        G.ParentID IS NULL

    UNION ALL

    -- Children
    SELECT
        GroupId = G.Id,
        ParentId = G.ParentId,
        GroupName = G.Name,
        HierarchyName = R.HierarchyName + '-' + G.Name,
        Level = R.Level + 1
    FROM
        GroupHierarchy AS R
        INNER JOIN [Group] AS G ON R.GroupId = G.ParentId
)
SELECT
    A.Id, 
    A.Name, 
    A.[Group], 
    G.HierarchyName, 
    A.Measure, 
    M.Name, 
    A.Price
FROM
    Article AS A
    INNER JOIN Measure AS M ON A.Measure = M.Measure
    INNER JOIN GroupHierarchy AS G ON A.[Group] = G.[GroupId]
OPTION 
    (MAXRECURSION 1000) -- Your max recursion depth

If it's still slow, you can try creating a physical (or temporal) table to store the recursion results using SELECT INTO, creating a clustered index and joining afterwards.