SQL문제풀기

[SQL 문제 풀이] Rearrange Products Table

May 08, 2025

[SQL 문제 풀이] Rearrange Products Table

Contents

내가 작성한 쿼리

Rearrange Products Table - LeetCode

Can you solve this real interview question? Rearrange Products Table - Table: Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store1 | int | | store2 | int | | store3 | int | +-------------+---------+ product_id is the primary key (column with unique values) for this table. Each row in this table indicates the product's price in 3 different stores: store1, store2, and store3. If the product is not available in a store, the price will be null in that store's column. Write a solution to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table. Return the result table in any order. The result format is in the following example. Example 1: Input: Products table: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 0 | 95 | 100 | 105 | | 1 | 70 | null | 80 | +------------+--------+--------+--------+ Output: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 0 | store1 | 95 | | 0 | store2 | 100 | | 0 | store3 | 105 | | 1 | store1 | 70 | | 1 | store3 | 80 | +------------+--------+-------+ Explanation: Product 0 is available in all three stores with prices 95, 100, and 105 respectively. Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2.

Rearrange Products Table - LeetCode

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

Rearrange Products Table - LeetCode

notion image


각 행이 (product_id, 스토어, 가격)을 가지도록 제품 표를 재배열하는 솔루션을 작성합니다.
스토어에서 제품을 사용할 수 없는 경우 결과 표에 해당 product_id와 스토어 조합이 포함된 행을 포함하지 마십시오.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

Oracle


SELECT * 
FROM Products
UNPIVOT
(
    price for store in
    (
        store1 as 'store1',
        store2 as 'store2',
        store3 as 'store3'
    )
)

MySQL


SELECT product_id, 'store1' AS store, store1 AS price
FROM Products
WHERE store1 IS NOT NULL

UNION ALL

SELECT product_id, 'store2', store2
FROM Products
WHERE store2 IS NOT NULL

UNION ALL

SELECT product_id, 'store3', store3
FROM Products
WHERE store3 IS NOT NULL;

차이점

UNPIVOT

열을 행으로 변환할 때 사용하는 연산
Oracle: 지원 O
MySQL: 지원 X → UNION ALL을 활용

Share article

Contents

내가 작성한 쿼리