LeetCode SQL - Pivot Table

Posted on Edited on

1179. Reformat Department 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
DROP TABLE IF EXISTS Department;
CREATE TABLE
    If NOT EXISTS Department (id INT, revenue INT, MONTH VARCHAR(5));
TRUNCATE TABLE Department;
INSERT INTO
    Department (id, revenue, MONTH)
VALUES
    ('1', '8000', 'Jan');
INSERT INTO
    Department (id, revenue, MONTH)
VALUES
    ('2', '9000', 'Jan');
INSERT INTO
    Department (id, revenue, MONTH)
VALUES
    ('3', '10000', 'Feb');
INSERT INTO
    Department (id, revenue, MONTH)
VALUES
    ('1', '7000', 'Feb');
INSERT INTO
    Department (id, revenue, MONTH)
VALUES
    ('1', '6000', 'Mar');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*Reformat the Department table
- Department PK: (id, month)

- SOLUTION: GROUP BY
 */
SELECT
    id,
    SUM(if (MONTH = 'Jan', revenue, NULL)) AS 'Jan_Revenue',
    SUM(if (MONTH = 'Feb', revenue, NULL)) AS 'Feb_Revenue',
    SUM(if (MONTH = 'Mar', revenue, NULL)) AS 'Mar_Revenue',
    SUM(if (MONTH = 'Apr', revenue, NULL)) AS 'Apr_Revenue',
    SUM(if (MONTH = 'May', revenue, NULL)) AS 'May_Revenue',
    SUM(if (MONTH = 'Jun', revenue, NULL)) AS 'Jun_Revenue',
    SUM(if (MONTH = 'Jul', revenue, NULL)) AS 'Jul_Revenue',
    SUM(if (MONTH = 'Aug', revenue, NULL)) AS 'Aug_Revenue',
    SUM(if (MONTH = 'Sep', revenue, NULL)) AS 'Sep_Revenue',
    SUM(if (MONTH = 'Oct', revenue, NULL)) AS 'Oct_Revenue',
    SUM(if (MONTH = 'Nov', revenue, NULL)) AS 'Nov_Revenue',
    SUM(if (MONTH = 'Dec', revenue, NULL)) AS 'Dec_Revenue'
FROM
    Department
GROUP BY
    id;

Dynamic pivot table (not available on LeetCode):

1
2
3
4
5
6
7
8
9
10
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;

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
25
26
27
28
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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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();