How to get last level of a hierarchical list, group by some field

Posted on

Question :

Given the schema:

create table containers (
    id int primary key, 
    name text, 
    product_id int, 
    lot int, 
    qty int, 
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Where containers is a hierarchical table with an unknown number of levels.

containers.parent_id = containers.id OR null

A container can hold other containers, this is a containers tree structure:

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   | 
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10] 

For us a container is considered as one expeditionary unit, depending on customer requirements a container can be a simple plastic bag of 10 pieces, or a full pallet with 10 boxes of 5000 pieces each one. Only containers of last level has a lot assigned, parent containers can hold different lots, but always belonging to same product.

We need to list all products included in a single order, group by lot.

  • Different products can’t have the same lot.
  • One container can only hold containers of the same product.

Sample data:

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

The order number 1 has two details lines, containers 16& 23, I need to get all lots included in these containers.

In this example the result should show this rows:

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

Group by lot:

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

I’ve created a rextester example with this values.

Answer :

This code shows how to use a recursive CTE, to return the results you’re looking for.

IF OBJECT_ID('dbo.OrdersItems') IS NOT NULL DROP TABLE dbo.OrdersItems;
IF OBJECT_ID('dbo.Containers') IS NOT NULL DROP TABLE dbo.Containers;

CREATE TABLE dbo.Containers
(
    ContainerID int NOT NULL
        CONSTRAINT PK_containers
        PRIMARY KEY CLUSTERED
    , ContainerName text NOT NULL
    , ProductID int NOT NULL
    , Lot int NULL
    , Quantity int NOT NULL
    , ParentContainerID int NULL
        CONSTRAINT FK_Containers_ContainerID
        FOREIGN KEY 
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE dbo.OrdersItems 
(
      OrderID INT NOT NULL
    , Position int NOT NULL
    , ContainerID int NULL
        CONSTRAINT FK_OrdersItems_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    , CONSTRAINT PK_OrdersItems 
        PRIMARY KEY CLUSTERED (OrderID, Position)
);

Insert the sample data:

INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID
                             , Lot, Quantity, ParentContainerID)
VALUES 
      (23, 'Box 40',  2, null, 40,  null)
    , (16, 'Pallet',  1, null, 120, null)
    , (12, 'Bag 20',  1, null, 20,  14)
    , (13, 'Bag 20',  1, null, 20,  14)
    , (14, 'Box 40',  1, null, 40,  16)
    , (19, 'Bag 20',  2, null, 20,  23)
    , (22, 'Bag 20',  2, null, 20,  23)
    , (5,  'Bag 20',  1, null, 20,  7)
    , (6,  'Bag 20',  1, null, 20,  7)
    , (7,  'Box 40',  1, null, 40,  16)
    , (1,  'Bag 10',  1, 500,  10,  5)
    , (2,  'Bag 10',  1, 501,  10,  5)
    , (3,  'Bag 10',  1, 502,  10,  6)
    , (4,  'Bag 10',  1, 500,  10,  6)
    , (8,  'Bag 10',  1, 600,  10,  12)
    , (9,  'Bag 10',  1, 601,  10,  12)
    , (10, 'Bag 10',  1, 502,  10,  13)
    , (11, 'Bag 10',  1, 501,  10,  13)
    , (15, 'Box 40',  1, 600,  40,  16)
    , (17, 'Bag 10',  2, 700,  10,  19)
    , (18, 'Bag 10',  2, 701,  10,  19)
    , (20, 'Bag 10',  2, 703,  10,  22)
    , (21, 'Bag 10',  2, 701,  10,  22);

INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID)
VALUES (1, 1, 16)
    , (1, 2, 23);

Here’s the recursive CTE:

;WITH RecursiveCTE AS
(
    SELECT c1.ContainerID
        , c1.ContainerName
        , c1.Lot
        , c1.ParentContainerID
        , c1.ProductID
        , c1.Quantity
        , Level = 1
    FROM dbo.Containers c1
    WHERE c1.ParentContainerID IS NULL
    UNION ALL
    SELECT c2.ContainerID
        , c2.ContainerName
        , c2.Lot
        , c2.ParentContainerID
        , c2.ProductID
        , c2.Quantity
        , Level = RecursiveCTE.Level + 1
    FROM dbo.Containers c2
        INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID
)
SELECT r.ProductID
    , r.Lot
    , QuantityTotal = SUM(r.Quantity)
FROM RecursiveCTE r 
WHERE r.Lot IS NOT NULL
    AND r.Level > 1
GROUP BY r.ProductID
    , r.Lot;

The results:

ProductID | Lot | QuantityTotal
--------: | --: | ------------:
        1 | 500 |            20
        1 | 501 |            20
        1 | 502 |            20
        1 | 600 |            50
        1 | 601 |            10
        2 | 700 |            10
        2 | 701 |            20
        2 | 703 |            10

As you can tell from the above code, I’ve renamed some of your columns to more clearly reflect their contents, and have added some minor formatting, along with foreign-key references.

db<>fiddle here

Leave a Reply

Your email address will not be published.