MySQL convert column to row unpivot example

In this post simulate pivot and unpivot in MySQL:

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:

id year value name
1 2015 10.1 flag1
1 2016 10.2 flag1
1 2017 10.3 flag1
1 2018 11 flag1
... ... ... ...

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:

name 2015 2016 2017 2018
flag3 0 G H y

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)
;

Related Article