How to unpivot the following table in Mysql? [Request for better approach]

Question

Initial table :

customer_id | order_A | order_B | order_C | order_D

       1 |       1 |       0 |       0 |       1 |
       2 |       0 |       1 |       0 |       1 |
       3 |       1 |       1 |       0 |       1 |
       4 |       0 |       0 |       0 |       1 |

Required Output :

customer_id | order_type |

       1 | A          |
       1 | D          |
       2 | B          |
       2 | D          |
       3 | A          |
       3 | B          |
       3 | D          |
       4 | D          |

Initially, my question was marked as duplicate and I was asked to refer the following question : MySQL pivot table

I referred it and also took help of http://archive.oreilly.com/oreillyschool/courses/dba1/ to come up with the following code:

select customer_id,
 case when order_A=1 then 'A' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_B=1 then 'B' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_C=1 then 'C' end as order_type
 from tb1 having order_type is not null
Union all
 select customer_id,
 case when order_D=1 then 'D' end as order_type
 from tb1 having order_type is not null order by customer_id,order_type;

This code is indeed giving me the required output, but I was wondering if there was a better way/approach to this question.

Also, it would be great help if someone can help suggest website/books where I can practise such question for interviews.

This kind of problem is symptomatic of poor design. But perhaps you already guessed that. Were you to create a table as per your desired result, you could then delete the source table and your problems would be solved — Strawberry, Dec 29 '18 at 08:44

Gordon Linoff · Accepted Answer · 2018-12-30T18:28:50.563

I would write this as:

select customerId, 'A' as order_type
from tb1
where order_A = 1
union all
select customerId, 'B' as order_type
from tb1
where order_B = 1
union all
select customerId, 'C' as order_type
from tb1
where order_C = 1
union all
select customerId, 'D' as order_type
from tb1
where order_D = 1;

The conditional logic is all in the where rather than split between a case expression and having clause. This also uses standard SQL, it will work in any database (your use of having with no GROUP BY is a MySQL extension).

If you want to simplify the query from a performance perspective, then you want to eliminate the four scans. You can do:

select tb1.customerId, o.order_type
from tb1 join
     (select 'A' as order_type union all
      select 'B' as order_type union all
      select 'C' as order_type union all
      select 'D' as order_type 
     ) o
     on (tb1.order_A = 1 and o.order_type = 'A') or
        (tb1.order_B = 1 and o.order_type = 'B') or
        (tb1.order_C = 1 and o.order_type = 'C') or
        (tb1.order_D = 1 and o.order_type = 'D') ;

This should read a row from tb1 and then do the four comparisons via the join operation. The union all approach reads the table four times.

Thank you Gordon for the help, can you please also recommend some website/books which can help me improve my SQL query writing skills. Thank in advance. — Hemanth Ravavarapu, Dec 30 '18 at 17:19

How to unpivot the following table in Mysql? [Request for better approach]

1 Answers1