Penggunaan UnPivot & CTE ( Common Table Expression ) Pada Mysql

 


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