CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expressionhierarchy
ItsWopak
ItsWopak

Reputation: 1

TSQL for getting a list of all product groups and the products in the groups

Get a list of all product groups and the products in the groups from a MS SQL Server. A product can exist in multiple product groups.

What i have so far, before i got lost :(

With CTE_Products As
(
    Select ProductGroupNo, Name, ParentProductGroupNo
    From [dbo].[ProductGroups]
    Where ParentProductGroupNo = 0

    Union All

    Select t.ProductGroupNo, t.Name, t.ParentProductGroupNo
    From [dbo].[ProductGroups] t
    Inner Join CTE_Products c On c.ProductGroupNo = t.ParentProductGroupNo
)

Select
    ProductGroupNo, ParentProductGroupNo
From
    CTE_Products
ORDER BY
    ProductGroupNo

SQL Tables

[Products] - Table with products
-ProductNo (INT)
-Description (VARCHAR)

ProductNo Description
1 Desc1
2 Desc2
3 Desc3
4 Desc4
5 Desc5

[ProductGroups] - Table with product groups, if ParentProductGroupNo is 0, the it's the root.
-ProductGroupNo (INT)
-Name (VARCHAR)
-ParentProductGroupNo (INT)

ProductGroupNo Name ParentProductGroupNo
1 Group1 0
2 Group2 0
3 Group3 1
4 Group4 1
5 Group5 2
6 Group6 3
7 Group7 5

[Products2Groups] - Table that ties products and groups. 1 product can exist in multiple groups.
-ProductNo (INT)
-ProductGroupNo (INT)

ProductNo ProductGroupNo
1 3
2 5
2 7
3 2
4 6
4 4
4 7

I'm looking for a Query (probably a CTE) that can give me a resultset that looks something like this:

ProductNo ProductGroup
1 Group1 ***** Group3
2 Group2 ***** Group5
2 Group2 ***** Group5 ***** Group7
3 Group2
4 Group1 ***** Group3 ***** Group6
4 Group1 ***** Group4
4 Group2 ***** Group5 ***** Group7
5

Upvotes: 0

Views: 482

Answers (1)

SantaRoza
SantaRoza

Reputation: 69

With this query, no matter how many parent levels you have, you can get the exact answer you want:

WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
    SELECT [ProductGroupNo] Id, Name, [ParentProductGroupNo] ParentId, CAST('' AS VARCHAR(MAX))
        FROM [dbo].[ProductGroups] AS FirtGeneration
        WHERE [ParentProductGroupNo] = 0   
    UNION ALL
    SELECT NextGeneration.[ProductGroupNo], NextGeneration.Name, Parent.ChildId,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(Parent.ChildName AS VARCHAR(MAX)) + ' ***** ' + NextGeneration.Name)
        ELSE(Parent.Parents + ' ***** ' + CAST(NextGeneration.Name AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM [dbo].[ProductGroups] AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.[ParentProductGroupNo] = Parent.ChildId    
)
SELECT pg.ProductNo , Case when Len(Parents) = 0 then ChildName else Parents end ProductGroup
    FROM Hierarchy h
    inner join [dbo].[Products2Groups] pg
on pg.ProductGroupNo = h.ChildId
order by pg.ProductNo

Upvotes: 0

Related Questions