Skip to content

2252. Dynamic Pivoting of a Table

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE PROCEDURE PivotProducts()
BEGIN
  # Override GROUP_CONCAT length which has a default limit of 1024.
  SET SESSION group_concat_max_len = 1000000;

  SELECT GROUP_CONCAT(
      DISTINCT CONCAT(
        'SUM(IF(store = "',
        store,
        '", price, NULL)) AS ',
        store
      )
    ) INTO @stmt
  FROM products;

  SET @query = CONCAT(
    'SELECT product_id, ', @stmt, ' '
    'FROM Products '
    'GROUP BY 1');

  PREPARE final_query FROM @query;
  EXECUTE final_query;
  DEALLOCATE PREPARE final_query;
END