CODEWITHSUNDEEP
sqloracle-databaseoracle10gpivot
NASIRUDDIN
NASIRUDDIN

Reputation: 41

How to convert column into rows in oracle 10g

Suppose I have the result of an Oracle sql query:

Month   Date
-----   -----
Jan     10
Jan     15
Jan     20
Feb     11
Feb     16
Feb     25

I want to display this data in the following format:

Jan Jan Jan Feb  Feb   Feb
10  15   20  11  16    25

How to write the query?

Upvotes: 3

Views: 2298

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Using PIVOT:

SQL> WITH sample_data AS(
  2  SELECT 'Jan' mnth, 10 dt FROM dual UNION ALL
  3  SELECT 'Jan' mnth, 15 dt FROM dual UNION ALL
  4  SELECT 'Jan' mnth, 20 dt FROM dual UNION ALL
  5  SELECT 'Feb' mnth, 11 dt FROM dual UNION ALL
  6  SELECT 'Feb' mnth, 16 dt FROM dual UNION ALL
  7  SELECT 'Feb' mnth, 25 dt FROM dual
  8  )
  9  -- end of smaple_data mimicking real table
 10  SELECT *
 11  FROM
 12    (SELECT dt, row_number() OVER(ORDER BY NULL) rn FROM sample_data
 13    ) PIVOT (MAX(dt) FOR (rn)
 14           IN (1 AS Jan_1, 2 AS jan_2, 3 AS Jan_3, 4 AS Feb_1, 5 Feb_2, 6 Feb_3));

     JAN_1      JAN_2      JAN_3      FEB_1      FEB_2      FEB_3
---------- ---------- ---------- ---------- ---------- ----------
        10         15         20         11         16         25

Under the hood PIVOT is same MAX + CASE. You can check it in 12c where Oracle added EXPAND_SQL_TEXT procedure to DBMS_UTILITY package.

SQL> VARIABLE c CLOB
SQL> BEGIN
  2    dbms_utility.expand_sql_text(Q'[WITH sample_data AS(
  3  SELECT 'Jan' mnth, 10 dt FROM dual UNION ALL
  4  SELECT 'Jan' mnth, 15 dt FROM dual UNION ALL
  5  SELECT 'Jan' mnth, 20 dt FROM dual UNION ALL
  6  SELECT 'Feb' mnth, 11 dt FROM dual UNION ALL
  7  SELECT 'Feb' mnth, 16 dt FROM dual UNION ALL
  8  SELECT 'Feb' mnth, 25 dt FROM dual
  9  )
 10  -- end of smaple_data mimicking real table
 11  SELECT *
 12  FROM
 13  (SELECT dt, row_number() OVER(ORDER BY NULL) rn FROM sample_data
 14  ) PIVOT (MAX(dt) FOR (rn)
 15  IN (1 AS Jan_1, 2 AS jan_2, 3 AS Jan_3, 4 AS Feb_1, 5 Feb_2, 6 Feb_3))]',:c);
 16  END;
 17  /

PL/SQL procedure successfully completed.

Now let's see what Oracle actually does internally:

SQL> set long 100000
SQL> print c

C
--------------------------------------------------------------------------------
SELECT "A1"."JAN_1" "JAN_1",
  "A1"."JAN_2" "JAN_2",
  "A1"."JAN_3" "JAN_3",
  "A1"."FEB_1" "FEB_1",
  "A1"."FEB_2" "FEB_2",
  "A1"."FEB_3" "FEB_3"
FROM
  (SELECT MAX(
    CASE WHE N ("A2"."RN"=1)
      THEN "A2"."DT"
    END ) "JAN_1",
    MAX(
    CASE
      WHEN ("A2"."RN"=2)
      THEN " A2"."DT"
    END ) "JAN_2",
    MAX(
    CASE
      WHEN ("A2"."RN"=3)
      THEN "A2"."DT"
    END ) "JAN_3" ,
    MAX(
    CASE
      WHEN ("A2"."RN"=4)
      THEN "A2"."DT"
    END ) "FEB_1",
    MAX(
    CASE
      WHEN ("A2". "RN"=5)
      THEN "A2"."DT"
    END ) "FEB_2",
    MAX(
    CASE
      WHEN ("A2"."RN"=6)
      THEN "A2"."DT"
    END ) "FEB_3"
  FROM
    (SELECT "A3"."DT" "DT",
      ROW_NUMBER() OVER ( ORDER BY NULL) " RN"
    FROM (
      (SELECT 'Jan' "MNTH",10 "DT" FROM "SYS"."DUAL" "A10"
      )
    UNION ALL (SE LECT 'Jan' "MNTH",15 "DT" FROM "SYS"."DUAL" "A9")
    UNION ALL
      (SELECT 'Jan' "MNTH",20 "DT" FROM "SYS"."DUAL" "A8"
      )
    UNION ALL
      (SELECT 'Feb' "MNTH",11 "DT" FROM " SYS"."DUAL" "A7"
      )
    UNION ALL
      (SELECT 'Feb' "MNTH",16 "DT" FROM "SYS"."DUAL" "A6"
      )
    UNION ALL
      (SELECT 'Feb' "MNTH",25 "DT" FROM "SYS"."DUAL" "A5"
      )) "A3"
    ) "A2"
  ) " A1"

Upvotes: 2

MT0
MT0

Reputation: 167822

WITH dates( month, day ) AS (
  SELECT 'Jan', 10 FROM DUAL UNION ALL
  SELECT 'Jan', 15 FROM DUAL UNION ALL
  SELECT 'Jan', 20 FROM DUAL UNION ALL
  SELECT 'Feb', 11 FROM DUAL UNION ALL
  SELECT 'Feb', 16 FROM DUAL UNION ALL
  SELECT 'Feb', 25 FROM DUAL
),
ordered_dates( month, day, seq_no ) AS (
  SELECT month,
         day,
         ROW_NUMBER() OVER ( PARTITION BY month ORDER BY day )
  FROM   dates
)
SELECT MAX( CASE WHEN month = 'Jan' AND seq_no = 1 THEN day END ) AS "Jan",
       MAX( CASE WHEN month = 'Jan' AND seq_no = 2 THEN day END ) AS "Jan",
       MAX( CASE WHEN month = 'Jan' AND seq_no = 3 THEN day END ) AS "Jan",
       MAX( CASE WHEN month = 'Feb' AND seq_no = 1 THEN day END ) AS "Feb",
       MAX( CASE WHEN month = 'Feb' AND seq_no = 2 THEN day END ) AS "Feb",
       MAX( CASE WHEN month = 'Feb' AND seq_no = 3 THEN day END ) AS "Feb"
FROM   ordered_dates;

Output:

       Jan        Jan        Jan        Feb        Feb        Feb
---------- ---------- ---------- ---------- ---------- ----------
        10         15         20         11         16         25

Upvotes: 1

Related Questions