Berikut adalah cara penggunaan UnPivot & CTE ( Common Table Expression ) Pada Mysql
1. UnPivot
select t.id,
c.col,
case c.col
when 'a' then a
when 'b' then b
when 'c' then c
end as data
from data t
cross join
(
select 'a' as col
union all select 'b'
union all select 'c'
) c;
Output :
2. CTE ( Common Table Expression )
with recursive cte as (
select id, product, quantity from mytable
union all
select id, product, quantity - 1 from cte where quantity > 1
)
select id, product from cte