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.

Answer 1

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.