CASE/WHEN-changing rows to columns

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: