MySQL - How to Unpivot Columns to Rows

Unpivot columns into rows in SQL

In Redshift, union all might be the simplest method:

select date, customer, shop, 'view' as activity, view as value 
from t
union all
select date, customer, shop, 'add' as activity, add as value 
from t
union all
select date, customer, shop, 'buy' as activity, buy as value 
from t;

You can also unpivot with case and cross join:

select t.date, t.customer, t.shop, x.activity,
       (case x.activity when 'view' then t.view when 'add' then t.add when 'buy' then t.buy end) as value
from t cross join
     (select 'view' as activity union all
      select 'add' as activity union all
      select 'buy' as activity
     ) x;

Note that aggregation is not necessary.

MySql : Convert Column data to row

select
  tablename.id,
  tablename.date
  ,SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.data, ' ', numbers.n), ' ', -1) name
from
  (
    SELECT @row := @row + 1 as n FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    (SELECT @row:=0) r
  ) numbers INNER JOIN Table1 tablename
  on CHAR_LENGTH(tablename.data)
     -CHAR_LENGTH(REPLACE(tablename.data, ' ', ''))>=numbers.n-1
order by
  id, n

Check link for output

http://sqlfiddle.com/#!9/fa0dcb/1

EXPLANATION:
First go through the inner query i.e.

select 0 
union all 
select 1 
union all 
select 3 
union all 
select 4 
union all 
select 5 
union all 
select 6 
union all 
select 6 
union all 
select 7 
union all 
select 8 
union all 
select 9

This will generate a table of 10 rows with 10 numbers.

Now the other query :

 SELECT @row := @row + 1 as n FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t1

Since above query is generating row numbers from below table 't' and table 't1' which is separated by ',' means that they are producing Cartesian product of their total rows.
For example: t have 10 rows and t1 also have 10 rows so, there Cartesian product produces 100 rows. So @row variable incremented 100 times and gives 100 rows of 100 numbers from 1 to 100.

The below query:

SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.data, ' ', numbers.n), ' ', -1)

this one will take "a b c d e f g h" one by one.

For example:
take numbers.n = 1
then inner substring_index will find index of first space and will return string before that index i.e. 'a'
and then outer substring_index will find the space from the end of the resulting string and will give the last character from the string i.e. 'a'.

Now if you
take numbers.n = 2
then inner substring_index will find index of first space and will return string before that index i.e. 'a b'
and then outer substring_index will find the space from the end of the resulting string and will give the last character from the string i.e. 'b'

Always try to breakdown the query like this and you will able to understand the query in simpler way.

mySQL how to unpivot dynamic columns

Sadly, mysql does not have an UNPIVOT function.

Your best option is the UNION ALL solution, like :

SELECT region, 'M07' AS month, M07 AS qty from stat
UNION ALL
SELECT region, 'M08', M08 AS from stat
UNION ALL 
SELECT region, 'M09', M09 AS from stat

It would be possible to create a piece of SQL code (as a stored procedure for example) that would generate this SQL query dynamically by first querying the INFORMATION_SCHEMA to retrieve the columns names, and using the results to build the query text.

UNPIVOT mysql data to other table

You need to UNPIVOT your data. MySQL doesn't have a built in function to do that so you'll need to use multiple queries.

INSERT INTO `monthlydata` (`id`, `year`, `monthName`, `stat_id`, `cat_id`, `data`) VALUES
SELECT id, year, 'January', stat_id, cat_id, January
    FROM monthly WHERE monthName = 'January'
UNION ALL 
SELECT id, year, 'February', stat_id, cat_id, February
    FROM monthly WHERE monthName = 'February'
UNION ALL 
SELECT id, year, 'March', stat_id, cat_id, March
    FROM monthly WHERE monthName = 'March'
.....

ID column here might cause issues. Depending on how you have defined it. If it is auto generated then you can remove it from the INSERT and let it be auto generated. Since you'll have rows for all months with same ID, you need to handle that scenario.

---

Related Topics