티스토리 뷰

공부

[SQL] pivot table

승가비 2020. 6. 18. 20:57
728x90
SELECT 
    id,
    sum(Jan_Revenue) Jan_Revenue,
    sum(Feb_Revenue) Feb_Revenue,
    sum(Mar_Revenue) Mar_Revenue,
    sum(Apr_Revenue) Apr_Revenue,
    sum(May_Revenue) May_Revenue,
    sum(Jun_Revenue) Jun_Revenue,
    sum(Jul_Revenue) Jul_Revenue,
    sum(Aug_Revenue) Aug_Revenue,
    sum(Sep_Revenue) Sep_Revenue,
    sum(Oct_Revenue) Oct_Revenue,
    sum(Nov_Revenue) Nov_Revenue,
    sum(Dec_Revenue) Dec_Revenue
FROM (
	SELECT id,
	CASE WHEN month='Jan' THEN revenue END AS Jan_Revenue,
	CASE WHEN month='Feb' THEN revenue END AS Feb_Revenue,
	CASE WHEN month='Mar' THEN revenue END AS Mar_Revenue,
	CASE WHEN month='Apr' THEN revenue END AS Apr_Revenue,
	CASE WHEN month='May' THEN revenue END AS May_Revenue,
	CASE WHEN month='Jun' THEN revenue END AS Jun_Revenue,
	CASE WHEN month='Jul' THEN revenue END AS Jul_Revenue,
	CASE WHEN month='Aug' THEN revenue END AS Aug_Revenue,
	CASE WHEN month='Sep' THEN revenue END AS Sep_Revenue,
	CASE WHEN month='Oct' THEN revenue END AS Oct_Revenue,
	CASE WHEN month='Nov' THEN revenue END AS Nov_Revenue,
	CASE WHEN month='Dec' THEN revenue END AS Dec_Revenue
	FROM Department
) result
GROUP BY id;

https://leetcode.com/problems/reformat-department-table/submissions/

 

Reformat Department Table - LeetCode

Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.

leetcode.com

SELECT 
	id,
	CASE WHEN COLLECT_SET(a)[0] IS NULL THEN 0 ELSE COLLECT_SET(a)[0] END AS a,
	CASE WHEN COLLECT_SET(b)[0] IS NULL THEN 0 ELSE COLLECT_SET(b)[0] END AS b,
	CASE WHEN COLLECT_SET(c)[0] IS NULL THEN 0 ELSE COLLECT_SET(c)[0] END AS c,
	CASE WHEN COLLECT_SET(d)[0] IS NULL THEN 0 ELSE COLLECT_SET(d)[0] END AS d
FROM (
	SELECT id,
	CASE WHEN type='a' THEN value END AS a,
	CASE WHEN type='b' THEN value END AS b,
	CASE WHEN type='c' THEN value END AS c,
	CASE WHEN type='d' THEN value END AS d
	FROM base
) result
GROUP BY id;

https://analyticshut.com/pivot-rows-to-columns-hive/

 

Pivot Rows to Columns in Hive | Transpose Table Easily

There are multiple use cases when we need to transpose/pivot table but Hive does not provide us with easy function to do so. Here is a workaround for it.

analyticshut.com

 

728x90

'공부' 카테고리의 다른 글

[Python] contains HashSet  (0) 2020.06.26
[Kafka] basic  (0) 2020.06.18
[Hive] SHOW TABLES FROM `db`  (0) 2020.06.18
[Java] lombok @ToString (CSV, Fields)  (0) 2020.06.18
[Python] flask redirect  (0) 2020.06.18
댓글