MySQL convert column to row unpivot example

In this post simulate pivot and unpivot in MySQL:

• Step 1 Colect all data by UNION ALL
• Step 2 Use wrap query to pivot data
• Database schema and data for MySQL Unpivot

Unpivot data is common operation in RDBMS. Some RDBMS like oracle has special function for this operation.Unfortunately MySQL does not support such function. Luckily this can be simulated by:

UNION ALL

Step 1: Colect all data by UNION ALL

First we need to collect all required information from our table:

  select id, year, flag1 value, 'flag1' name
  from fiscal
  union all
  select id, year, flag2 value, 'flag2' name
  from fiscal
  union all
  select id, year, flag3 value, 'flag3' name
  from fiscal
  union all
  select id, year, result value, 'result' name
  from fiscal

result is aggregated information for all rows:

Step 2: Use wrap query to pivot data

First we need to collect all required information from our table:

  select id, year, flag1 value, 'flag1' descrip
  from fiscal
  union all
  select id, year, flag2 value, 'flag2' descrip
  from fiscal
  union all
  select id, year, flag3 value, 'flag3' descrip
  from fiscal
  union all
  select id, year, result value, 'result' descrip
  from fiscal

result is aggregated information for all rows:

Database schema and data for MySQL Unpivot

This is the table and the information used in this example. You can simply recreate the table and use the SQL in step 2.

CREATE TABLE fiscal
	(`id` int, `year` int(4), `flag1` varchar(5), `flag2` varchar(3), `flag3` varchar(3), `result` int(2))
;

INSERT INTO fiscal
	(`id`, `year`, `flag1`, `flag2`, `flag3`, `result`)
VALUES
	(1, '2015', '10.1', 'B', NULL, 1),
	(1, '2016', '10.2', 'A', 'G', 1),
	(1, '2017', '10.3', 'D', 'H', 2),
	(1, '2018', '11', 'C', 'y', 3)
;