Search code examples
mysqlsqlunpivot

need help writing a query (restructuring the table)

I need to write a select statement that will rewrite the table in the following manner... I'm not sure how to go about this using MySQL.

Example of table

user_id   date         a    b    c     
123456    2020-01-01   1    1    1
234567    2020-03-04   1    0    0
453576    2020-05-05   1    0    1

Desired result

user_id   date        results
123456    2020-01-01  a
123456    2020-01-01  b
123456    2020-01-01  c
234567    2020-03-04  a
453576    2020-05-05  a
453576    2020-05-05  c
Solution

  • In MySQL you can unpivot with union all, while filtering on 1 values:

    select user_id, date, 'a' as result from mytable where a = 1
union all select user_id, date, 'b' from mytable where b = 1
union all select user_id, date, 'c' from mytable where c = 1
order by user_id, date, result