[leetcode] 1795. Rearrange Products Table

https://leetcode.com/problems/rearrange-products-table

 

 

 

MySQL은 기본적으로 PIVOT/UNPIVOT 기능을 제공하지 않기 때문에,

CASE 문이나 UNION ALL 등을 이용하여 구현한다.

pivot, unpivot 을 구현할 때 실제 데이터를 쿼리에 사용하는 하드 코딩이 요구된다.

이 문제는 열을 행으로 바꾸는 unpivot 을 구현하여 해결된다.

행으로 만들고싶은 열의 개수만큼 select 한 후 모두 union 혹은 union all 로 묶어주면 된다.

 

반대로 행을 열로 바꾸는 pivot 은 group by 와 case 문을 사용하여 구현 가능하다.

각 행을 group 으로 묶어주고 조건에 맞게 case 문을 작성한 후,

max 혹은 sum 등의 집계함수 처리하여 열을 만든다.

 

 

-- unpivot (열을 행으로 바꾸기)
select product_id, 'store1' as store, store1 as price
from Products
where store1 is not null
union
select product_id, 'store2' as store, store2 as price
from Products
where store2 is not null
union
select product_id, 'store3' as store, store3 as price
from Products
where store3 is not null

-- pivot (행을 열로 바꾸기)
select product_id,
    max(case when store='store1' then price else null end) as store1,
    max(case when store='store2' then price else null end) as store2,
    max(case when store='store3' then price else null end) as store3,
from pivot_table
group by product_id

 

 

 

---

 

< English (feedback by ChatGPT) >

 

 

MySQL basically doesn't support pivot or unpivot clauses

(MySQL doesn't natively support PIVOT or UNPIVOT clauses,)

 

but we could implement them using case or union all clauses

(but we can simulate them using CASE statements or UNION ALL.)

 

Hard coding with the actual data in the table is needed to implement pivot, unpivot clauses

(To implement pivoting or unpivoting, we usually need to hard-code actual column names from the data.)

 

This problem is solved by implementing an unpivot clause which changes columns to rows.

(This problem can be solved by implementing an UNPIVOT, which transforms columns into rows.)

 

Use select for a number of columns we want to change to rows, and use union or union all to combine.

(We SELECT each column we want to unpivot and combine them using UNION or UNION ALL.)

 

On the other hands, It's possible to implement a pivot clause which changes rows to columns by groupby and case causes.

(Conversely, a PIVOT (turning rows into columns) can be simulated using GROUP BY and CASE statements.)

 

Group by each rows, create case causes for each according to a condition,

(We group rows, write CASE expressions based on conditions,)

 

and make columns by some aggregate causes like max or sum

(and apply aggregate functions like MAX or SUM to create the resulting columns.)