pivot table - 행렬변환

SELECT COUNTRY
       ,  SUM(CASE WHEN activity='Adventure park' THEN num_of_places ELSE 0 END) as adventure_park
       ,  SUM(CASE WHEN activity='Golf' THEN num_of_places ELSE 0 END) as golf
       ,  SUM(CASE WHEN activity='River cruise' THEN num_of_places ELSE 0 END) as River_cruise
       ,  SUM(CASE WHEN activity='Kart racing' THEN num_of_places ELSE 0 END) as Kart_racing       
FROM 
(
 WITH pivot_table AS
	(
		SELECT 1 AS id, 'France' AS country, 'Normandy' AS region, 'River cruise' AS activity, 2 AS num_of_places FROM DUAL
		UNION ALL SELECT 2 AS id, 'Germany' AS country, 'Bavaria' AS region, 'Golf' AS activity, 5 AS num_of_places FROM DUAL
		UNION ALL SELECT 3 AS id, 'Germany' AS country, 'Berlin' AS region, 'Adventure park' AS activity, 2 AS num_of_places FROM DUAL 
		UNION ALL SELECT 4 AS id, 'France' AS country, 'lie-de-France' AS region, 'River cruise' AS activity, 1 AS num_of_places FROM DUAL
		UNION ALL SELECT 5 AS id, 'Sweden' AS country, 'Stockholm' AS region, 'River cruise' AS activity, 3 AS num_of_places FROM DUAL
		UNION ALL SELECT 6 AS id, 'France' AS country, 'Normandy' AS region, 'Kart racing' AS activity, 4 AS num_of_places FROM DUAL
	)
	SELECT * FROM pivot_table
)
GROUP BY COUNTRY
ORDER BY COUNTRY
;