Row level data to Column level using CASE statement
SELECT TRUNC(SALES_DATE,'MON') AS SALES_MONTH,
SUM(CASE WHEN PRODUCT_ID = 100 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_100,
SUM(CASE WHEN PRODUCT_ID = 101 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_101,
SUM(CASE WHEN PRODUCT_ID = 105 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_105,
SUM(CASE WHEN PRODUCT_ID = 106 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_106,
SUM(CASE WHEN PRODUCT_ID = 200 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_200
FROM SALES
GROUP BY TRUNC(SALES_DATE, 'MON')
ORDER BY TRUNC(SALES_DATE,'MON')
Row level data to Column level using PIVOT
SELECT *FROM
(SELECT TRUNC(SALES_DATE,'MON') AS SALES_MONTH, PRODUCT_ID, TOTAL_AMOUNT FROM SALES)
PIVOT (SUM(TOTAL_AMOUNT) FOR PRODUCT_ID IN ('100' AS P_100, '101' AS P_101, '105' AS P_105, '106' AS P_106, '200' AS P_200))
ORDER BY SALES_MONTH
Row level data to Column level using LISTAGG
SELECT REGION,
LISTAGG(LAST_NAME, ',') WITHIN GROUP (ORDER BY LAST_NAME) AS CUSTOMER_NAMES
FROM CUSTOMER
GROUP BY REGION
Column level data to Row level using UNION
CREATE TABLE SALES_PIVOT AS
SELECT TRUNC(SALES_DATE,'MON') AS SALES_MONTH,
SUM(CASE WHEN PRODUCT_ID = 100 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_100,
SUM(CASE WHEN PRODUCT_ID = 101 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_101,
SUM(CASE WHEN PRODUCT_ID = 105 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_105,
SUM(CASE WHEN PRODUCT_ID = 106 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_106,
SUM(CASE WHEN PRODUCT_ID = 200 THEN TOTAL_AMOUNT ELSE 0 END) AS TOTAL_200
FROM SALES
GROUP BY TRUNC(SALES_DATE, 'MON')
ORDER BY TRUNC(SALES_DATE,'MON')
SELECT SALES_MONTH, 100 AS PRODUCT_ID, TOTAL_100 AS TOTAL_AMOUNT FROM SALES_PIVOT
UNION ALL
SELECT SALES_MONTH, 101 AS PRODUCT_ID, TOTAL_101 AS TOTAL_AMOUNT FROM SALES_PIVOT
UNION ALL
SELECT SALES_MONTH, 105 AS PRODUCT_ID, TOTAL_105 AS TOTAL_AMOUNT FROM SALES_PIVOT
UNION ALL
SELECT SALES_MONTH, 106 AS PRODUCT_ID, TOTAL_106 AS TOTAL_AMOUNT FROM SALES_PIVOT
UNION ALL
SELECT SALES_MONTH, 200 AS PRODUCT_ID, TOTAL_200 AS TOTAL_AMOUNT FROM SALES_PIVOT
Column
level data to
Row
level using UNPIVOT
SELECT SALES_MONTH, PRODUCT_ID, TOTAL_AMOUNT
FROM SALES_PIVOT
UNPIVOT( TOTAL_AMOUNT FOR PRODUCT_ID IN (TOTAL_100 AS '100', TOTAL_101 AS '101', TOTAL_105 AS '105', TOTAL_106 AS '106',TOTAL_200 AS '200'))
