LeetCode SQL - Pivot Table

Table of contents

Pivot

1795. Rearrange Products Table

SELECT
    product_id,
    'store1' AS store,
    store1 AS price
FROM Products
WHERE store1 IS NOT NULL
UNION
SELECT
    product_id,
    'store2' AS store,
    store2 AS price
FROM Products
WHERE store2 IS NOT NULL
UNION
SELECT
    product_id,
    'store3' AS store,
    store3 AS price
FROM Products
WHERE store3 IS NOT NULL

2252. Dynamic Pivoting of a Table

DROP TABLE if EXISTS Products;
CREATE TABLE
    If NOT EXISTS Products (product_id INT, store VARCHAR(8), price INT);
TRUNCATE TABLE Products;
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('1', 'Shop', '110');
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('1', 'LC_Store', '100');
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('2', 'Nozama', '200');
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('2', 'Souq', '190');
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('3', 'Shop', '1000');
INSERT INTO
    Products (product_id, store, price)
VALUES
    ('3', 'Souq', '1900');

create procedure PivotProducts()
begin
    set session group_concat_max_len = 1000000; # default is 1024
    
 set @sql = null;
 select group_concat(
  distinct concat(
   'sum(if(store = "', store, '", price, null)) as ', store
  )
 )
 into @sql
 from Products;

 set @sql = concat('select product_id, ', @sql, ' from Products group by 1 order by null');

 prepare stmt from @sql;
 execute stmt;
 deallocate prepare stmt;
end

For MySQLWorkbench test:

drop procedure if exists PivotProducts;

delimiter //

create procedure PivotProducts()
begin
 set session group_concat_max_len = 1000000; # default is 1024

 set @sql = null;
 select group_concat(
  distinct concat(
   'sum(if(store = "', store, '", price, null)) as ', store
  ) separator ','
 )
 into @sql
 from Products;

 set @sql = concat('select product_id, ', @sql, ' from Products group by 1');

 prepare stmt from @sql;
 execute stmt;
 deallocate prepare stmt;
end //

delimiter ;

call PivotProducts();

Unpivot

1179. Reformat Department Table

SELECT
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
    SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
    SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS May_Revenue,
    SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
    SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
    SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
    SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
    SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
    SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
    SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM department
GROUP BY id

Dynamic pivot table (not available on LeetCode):

select @sql = null;
select group_concat(distinct concat("sum(if(month = '", month, "', revenue, null)) as ", month, "_Revenue")) from Department
into @sql;

select concat("select id, ", @sql, " from Department group by 1")
into @sql;

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;