[1795_EASY] Rearrange Products Table

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
product_id is the primary key 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 an SQL query 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 query 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.

문제 조건 

 

각 행이(product_id, store,price)를 갖도록 Products 테이블을 재정렬 하시오.

해당 상점에 제품이 없는 경우 결과 테이블에 해당 product_id및 상점 조합이 있는 행을 포함하지 마시오.

 

풀이 과정 

select product_id ,
        'store1' store,
        store1 as 'price'
from products 
where store1 is not null

union

select product_id,
        'store2' store,
        store2 as 'price'
from products 
where store2 is not null

union

select product_id,
        'store3' store,
        store3 as 'price'
from products 
where store3 is not null

 

ORACLE 에서 UNPIVOT으로 풀어보기 

- UNPIVOT EXCLUDE NULLS? : NULL을 포함시키기 위함

 

https://dejavuhyo.github.io/posts/oracle-unpivot/

Oracle UNPIVOT

SELECT 
    product_id,
    store,
    price
FROM Products 
UNPIVOT EXCLUDE NULLS (price FOR store IN (store1 AS 'store1', store2 AS 'store2', store3 AS 'store3'));