Conditional Aggregation within Dynamic Pivot Statement

user_14402287 0
Viewed 140

I have 2 tables say:

Orders:
id | Name | Amount | Date
1  | ABC  | 100    | 2020-10-01
2  | XYZ  | 200    | 2020-10-01
3  | MNO  | 250    | 2020-11-01

Order_details:
id | Item | Qty
1  | A    | 2
1  | B    | 1
1  | C    | 3
2  | X    | 1
3  | A    | 4

Now I want to fetch the data using the date on which the order was made.

Say if I want to fetch the data of 2020-10-01 the output should be something like this:

id | Name | Amount | Date       | Item1 | Qty | Item2 | Qty ...
1  | ABC  | 100    | 2020-10-01 | A     | 2   | B     | 1
2  | XYZ  | 200    | 2020-10-01 | X     | 1

I tried to fetch it using a subquery, but I was not sure how to print that data.

Thanks in advance!

aggregate-functions pivot inner-join mysql sql
2 Answers

You can use Conditional Aggregation within Dynamic Pivot Statement which works even for DB version 5.5 :

SET SESSION group_concat_max_len = 18446744073709551615;
SET @sql = NULL;
SET @date = '2020-10-01';

SELECT GROUP_CONCAT(
           DISTINCT
             CONCAT(
                    'MAX(CASE WHEN rn = ', rn,' THEN Item END ) AS Item', rn,
                    ', MAX(CASE WHEN rn = ', rn,' THEN Qty END ) AS Qty'
                    )
       )
  INTO @sql
  FROM ( 
        SELECT *, @rn := IF(@i = id, @rn + 1, 1) AS rn, @i := id
          FROM Order_details
          JOIN (SELECT @i := 0, @rn := 0) i
         ORDER BY id, Item
  ) od;

SET @sql = CONCAT('SELECT o.id, o.name, o.amount, o.date,',@sql,
                   ' FROM Orders o
                     JOIN (
                           SELECT *, @rn := IF(@i = id, @rn + 1, 1) AS rn, @i := id
                             FROM Order_details
                             JOIN (SELECT @i := 0, @rn := 0) i
                            ORDER BY id, Item    
                          ) od
                       ON od.id = o.id
                    WHERE o.date = "',@date,'"
                    GROUP BY o.id, o.name, o.amount, o.date
                    ORDER BY o.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

where parameter value might be updated within the second line ( SET @date = '2020-10-01'; ) . Btw, the function GROUP_CONCAT() has an upper length limit(for the parameter group_concat_max_len with default value of 1024) that might be updated(upto the max value of 18446744073709551615) for the current session for the cases the table has multiple distinct items, so having lots of columns.

Demo

user_58413060

For a fixed maximum number of items per orders, you can use window functions and conditional aggregation:

select o.*,
    max(case when od.rn = 1 then item end) item1,
    max(case when od.rn = 1 then qty  end) qty1,
    max(case when od.rn = 2 then item end) item2,
    max(case when od.rn = 2 then qty  end) qty2,
    max(case when od.rn = 3 then item end) item3,
    max(case when od.rn = 3 then qty  end) qty3
from orders o
inner join (
    selet od.*, row_number() over(partition by id order by item) rn
    from order_details od
) od on od.id = o.id
group by o.id

You can extend the select clause with more conditional expressions to handle more than 3 items per order.

Note that window functions are available in MySQL 8.0 only.

user_106767160
Related