This discussion is archived
8 Replies Latest reply: Jun 30, 2009 5:03 PM by 481319 RSS

How to transpose the table?

684278 Newbie
Currently Being Moderated
Hi, guys.

Could you please help me to resolve the following problem:i have the following input table
WITH input_table
AS
  (
    select 1 A, 2 B, 3 C from dual
    union all
    select 4, 5, 6 from dual
    union all
    select 7, 8, 9 from dual
  )
SELECT *
FROM input_table;
I need to receive the following table using ONLY sql syntax (without any procedures):
WITH result_table
AS
  (
    select 7 A, 4 B, 1 C from dual
    union all
    select 8, 5, 2 from dual
    union all
    select 9, 6, 3 from dual
  )
SELECT *
FROM result_table;
Wait for your suggestons. Thanks.

Victor
  • 1. Re: How to transpose the table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Viictor,

    Are you saying you want to display the first table so that it look like the second table?

    Displaying rows as columns is called a pivot.
    Displaying columns as rows is called unpivot.

    [This thread|http://forums.oracle.com/forums/message.jspa?messageID=3227388#3227388] shows you the basics of pivoting. It uses the COUNT function, but for your purpose you'll want MIN or MAX.

    Search for "pivot" (or "rows to columns") and "unpivot" for more examples.
  • 2. Re: How to transpose the table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Victor,

    Here's what you requested:
    WITH     cntr         AS
    (
         SELECT     LEVEL     AS r_num
         FROM     dual
         CONNECT BY     LEVEL <= 3
    )
    ,     unpivoted     AS
    (
         SELECT     DENSE_RANK () OVER (ORDER BY i.a)     AS c_num
         ,     c.r_num
         ,     CASE     c.r_num
                   WHEN  1          THEN i.a
                   WHEN  2          THEN i.b
                   WHEN  3          THEN i.c
              END     AS val
         FROM          cntr          c
         CROSS JOIN     input_table     i
    )
    SELECT       MIN (CASE WHEN c_num = 1 THEN val END)     AS a
    ,       MIN (CASE WHEN c_num = 2 THEN val END)     AS b
    ,       MIN (CASE WHEN c_num = 3 THEN val END)     AS c
    FROM       unpivoted
    GROUP BY  r_num
    ORDER BY  r_num;
    The trick with pivots is to have some columns that identify precisely to what row and column each item of data should be directed.
    In this example, every data item that was originally in column a will be on the 1st row, everything that was originally in column b will go to the 2nd row, and everything that was in column c will go to the 3rd row. Besides unpivoting the data (so that a, b and c are all in the same column, val) the sub-query called unpivoted assigns the row number r_num.

    In the example above, I directed everything that was originally on the row with the lowest a value to the 1st column, everything that was originally on the row with the 2nd lowest a to the 2nd column, and everything that was on the row with the 3rd lowest a to the 3rd column. I could have used column b or c just as well as column a; for this small set of sample data, the results happen to be the same.

    This solution assumes you know exactly how many rows are in the original table, and therefore how many columns will be in the result set.
    If you don't know that, you can write dynamic SQL to write the query for you, with exactly the right number of "MIN (CASE ...) AS ..." expressions as needed.
    There are several other techniques for dealing with variable numbers of output columns.

    Edited by: Frank Kulash on Jun 26, 2009 3:19 PM
    Sorry; I mis-read your message. See Christian's correction, below.
  • 3. Re: How to transpose the table?
    Christian Balz Pro
    Currently Being Moderated
    Great solution Frank. Congratulations. My suggestion is you change the sequence of numbers to get the desired result of OP.
    SELECT       MIN (CASE WHEN c_num = 3 THEN val END)     AS a
         ,       MIN (CASE WHEN c_num = 2 THEN val END)     AS b
         ,       MIN (CASE WHEN c_num = 1 THEN val END)     AS c
    output
             A          B          C
    ---------- ---------- ----------
             7          4          1
             8          5          2
             9          6          3
    Best regards,
    Christian Balz
  • 4. Re: How to transpose the table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Christian,
    Christian Balz wrote:
    ... My suggestion is you change the sequence of numbers to get the desired result of OP.
    Thank you! That was a mistake.
  • 5. Re: How to transpose the table?
    MichaelS Guru
    Currently Being Moderated
    Or
    SQL>  with input_table
    as
      (
        select 1 a, 2 b, 3 c from dual union all
        select 4, 5, 6 from dual union all
        select 7, 8, 9 from dual
      )
    ---
    ---
    select case rownum when 1 then lead(a,2) over (order by rownum) when 2 then lead(b) over (order by rownum) when 3 then c end a,
           case rownum when 1 then lead(a) over (order by rownum) when 2 then b when 3 then lag(c) over (order by rownum) end b,
           case rownum when 1 then a when 2 then lag(b) over (order by rownum) when 3 then lag(c,2) over (order by rownum) end c
    from input_table
    /
             A          B          C
    ---------- ---------- ----------
             7          4          1
             8          5          2
             9          6          3
    
    3 rows selected.
  • 6. Re: How to transpose the table?
    684278 Newbie
    Currently Being Moderated
    Hi, guys!

    Please sorry for delay with answer.

    Thanks a lot for your anwers.

    Frank! your answer is very interesing, detailed and useful, you always give very good and interesting anwers, thanks a lot.

    Frank, could you please tell me more detailed how to realize such method using dynamic sql? (when I don't know exactly the number of the columns in thee input table)

    Christian, thanks for important direction about Frank decision.

    Michaels2, thanks a lot for such original decision, but i think it's not so flexible (because we can don't know exactly the number of the columns)

    Thanks to all.

    Victor
  • 7. Re: How to transpose the table?
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Victor,

    So you want to do something like this
    WITH     cntr         AS
    (
         SELECT     LEVEL     AS r_num
         FROM     dual
         CONNECT BY     LEVEL <= 3
    )
    ,     unpivoted     AS
    (
         SELECT     DENSE_RANK () OVER (ORDER BY i.a)     AS c_num
         ,     c.r_num
         ,     CASE     c.r_num
                   WHEN  1          THEN i.a
                   WHEN  2          THEN i.b
                   WHEN  3          THEN i.c
              END     AS val
         FROM          cntr          c
         CROSS JOIN     input_table     i
    )
    SELECT       MIN (CASE WHEN c_num = 1 THEN val END)     AS a
    ,       MIN (CASE WHEN c_num = 2 THEN val END)     AS b
    ,       MIN (CASE WHEN c_num = 3 THEN val END)     AS c
    FROM       unpivoted
    GROUP BY  r_num
    ORDER BY  r_num;
    but dynamic.

    Review [this thread|http://forums.oracle.com/forums/message.jspa?messageID=3266891#3266891] for how to do a preliminary query to generate dynamic parts of the main query.

    In this case, you'll have to do 2 prelimnary queries.

    (1) In the static example above, I knew there were three columns in the original table, and that they were called a, b and c.
    I used that information to code these lines:
    ...               WHEN  1          THEN i.a
                   WHEN  2          THEN i.b
                   WHEN  3          THEN i.c
    One preliminary query will have to produce something similar, perhaps by doing a query on all_tab_columns, which has one row for every column in a table. These columns should all be the same data type. If they are not, you should probably use TO_CHAR to make VARCHAR2 values out of the ones that are not already strings. All_tab_columns.data_type will tell you the type of each column.

    (2) In the static query above, I knew that there were three rows in the original table.
    I used that information to write these lines:
    SELECT       MIN (CASE WHEN c_num = 1 THEN val END)     AS a
    ,       MIN (CASE WHEN c_num = 2 THEN val END)     AS b
    ,       MIN (CASE WHEN c_num = 3 THEN val END)     AS c
    The other preliminary query will have to figure out how many rows were in the original table, and write some code like this.
    Note that the column aliases (a, b, and c) here are not related to the original column names. It is pure coincidence that the original column names also happened to be called a, b and c. I don't know how you want to name the output columns: using letters like A, B and C isn't a bad idea, and it's easy to do using CHR (ASCII ('A') + n - 1) to get the n-th name, but this only works if you have 26 (or fewer) output columns.
    'col_' || TO_CHAR (n)
    will work for any number of columns.

    There are two other things that are hard-coded into the query above that cannot be hard-coded in your dynamic query:
    The m"magic number" 3 in cntr:
    ...     CONNECT BY     LEVEL <= 3
    This is the number of columns in the origianal table, and can be replace with a scalar sub-query:
    ...     CONNECT BY     LEVEL <= ( SELECT  COUNT (*)
                                 FROM        all_tab_cols
                           WHERE   owner     = '&owner_name'
                           AND        table_name     = '&table_name'
                         )
    The other is the column that deterrmines the final row order in unpivoted (a in this exampe):
    ...     SELECT     DENSE_RANK () OVER (ORDER BY i.a)     AS c_num
    If you want to use the first column, then you can get that name in a substitution variable.
    Before doing the main query, do this to set the substitution variablle first_o_col_name:
    COLUMN     first_o_col_name_col     NEW_VALUE     first_o_col_name
    
    SELECT     column_name     AS first_o_col_name_col
    FROM     all_tab_cols
    WHERE     owner          = '&owner_name'
    AND     table_name     = '&table_name'
    AND     column_id     = 1;
    Then, in the main query:
    ...     SELECT     DENSE_RANK () OVER (ORDER BY i.&first_o_col_name)     AS c_num
    Edited by: Frank Kulash on Jun 30, 2009 6:59 PM
  • 8. Re: How to transpose the table?
    481319 Guru
    Currently Being Moderated
    Unconventional way
    SQL> r
      1  WITH input_table
      2  AS
      3    (
      4      select 1 A, 2 B, 3 C from dual
      5      union all
      6      select 4, 5, 6 from dual
      7      union all
      8      select 7, 8, 9 from dual
      9    )
     10  Select a, b, c
     11  from (
     12  SELECT L ,
     13  Lead(regexp_substr(A||','||B||','||C||','
     14     ,'[^,]+',1,L),2 ) over ( partition by L order by rownum ) A
     15  ,Lead(
     16  regexp_substr(A||','||B||','||C||','
     17     ,'[^,]+',1,L),1 ) over ( partition by L order by rownum ) B
     18  ,Lead(
     19  regexp_substr(A||','||B||','||C||','
     20     ,'[^,]+',1,L),0 ) over ( partition by L order by rownum ) C
     21  FROM input_table
     22  , ( select level L from dual connect by level <=3)
     23  )
     24* Where a is not null
    
    A          B          C
    ---------- ---------- ----------
    7          4          1
    8          5          2
    9          6          3
    Need to test more this way

    SS

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points