Hi ,
In this entry i am gonna show how to change row to colums using case when statement.
At first lets say we have a following table
CREATE TABLE TEST_1
(
FACTORY_NAME VARCHAR(100 CHAR),
SALES NUMBER(20),
PRODUCTION NUMBER(20)
)
Current View:
What i want :
Step1:
creating a dummy table
(
SELECT
'TOTAL_SALES' AS TEXT FROM DUAL
)
UNION
(
SELECT
'TOTAL_PRODUCTION' AS TEXT FROM DUAL
)
Step2:
Combining it with my main table (no join needed )
SELECT
*
FROM
(
(
SELECT
'TOTAL_SALES' AS TEXT FROM DUAL
)
UNION
(
SELECT
'TOTAL_PRODUCTION' AS TEXT FROM DUAL
)
),
(
SELECT
FACTORY_NAME ,
SALES AS TOTAL_SALES,
PRODUCTION AS TOTAL_PRODUCTION
FROM
TEST_1
)
Step 3:
Adding Case when structure to prepare main view
SELECT
TEXT,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_1' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1' THEN TOTAL_PRODUCTION
END AS FACTORY_1,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_2' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2' THEN TOTAL_PRODUCTION
END AS FACTORY_2,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_3' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3' THEN TOTAL_PRODUCTION
END AS FACTORY_3
FROM
(
(
SELECT
'TOTAL_SALES' AS TEXT FROM DUAL
)
UNION
(
SELECT
'TOTAL_PRODUCTION' AS TEXT FROM DUAL
)
),
(
SELECT
FACTORY_NAME ,
SALES AS TOTAL_SALES,
PRODUCTION AS TOTAL_PRODUCTION
FROM
TEST_1
)
,
Step 4:
Getting rid of null values. ( can be done with filtering too )
SELECT
TEXT , SUM(FACTORY_1) FACTORY_1 ,SUM(FACTORY_2) FACTORY_2 ,SUM(FACTORY_3) FACTORY_3
FROM
(
SELECT
TEXT,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_1' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1' THEN TOTAL_PRODUCTION
END AS FACTORY_1,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_2' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2' THEN TOTAL_PRODUCTION
END AS FACTORY_2,
CASE
WHEN TEXT='TOTAL_SALES' AND FACTORY_NAME='FACTORY_3' THEN TOTAL_SALES
WHEN TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3' THEN TOTAL_PRODUCTION
END AS FACTORY_3
FROM
(
(
SELECT
'TOTAL_SALES' AS TEXT FROM DUAL
)
UNION
(
SELECT
'TOTAL_PRODUCTION' AS TEXT FROM DUAL
)
),
(
SELECT
FACTORY_NAME ,
SALES AS TOTAL_SALES,
PRODUCTION AS TOTAL_PRODUCTION
FROM
TEST_1
)
)
GROUP BY TEXT
ORDER BY TEXT DESC
Final View: