7 Replies Latest reply: Mar 19, 2014 6:18 AM by user10412263 RSS

    Convert row into column

    fame

      Hi all,

                 select * from t_temp;

       

                  col1     col2   col3

      ---------------------------------

                 100      a       1

                 101      b        2

                 103      c        3

       

       

      Wanted output,

                     a           b         c

                    -----------------------

                     1           2          3

       

      Regards,

      Fame

        • 1. Re: Convert row into column
          Moazzam

          If you are using 10g then use decode function as following:

           

          transpose-rows-to-columns-in-oracle-10g

          Pivot function in Oracle 10g???

           

          If you are using 11g, the use pivot function:

           

          sql - How to convert Columns into Rows in Oracle?

          • 3. Re: Convert row into column
            Hoek

              select max(case when c2 = 'a' then c3 end) a

              ,      max(case when c2 = 'b' then c3 end) b

              ,      max(case when c2 = 'c' then c3 end) c

              from   t_temp;

             

                     A          B          C

            ---------- ---------- ----------

                     1          2          3

             

            1 row selected.

            • 4. Re: Convert row into column
              fame

              here they mentioned values in statement,

               

              MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
              MAX
              (DECODE(document_type, 'Pan card', document_id)) AS pan_card,
              MAX
              (DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence

               

              the values may differ, i want form the query dynamically

              • 5. Re: Convert row into column
                user10412263

                You can use pivot function

                • 6. Re: Convert row into column
                  Hoek

                  SQL> create or replace procedure my_pivot( p_cursor in out sys_refcursor

                    2  )

                    3  as

                    4    l_query long := 'select ';

                    5    i number := 0;

                    6  begin

                    7    for x in ( select c2

                    8               from   t_temp

                    9             )

                  10    loop

                  11      i := i+1;

                  12      if i=1

                  13      then

                  14        l_query := l_query ||replace( q'| max(decode(c2,'$X$',c3)) $X$|', '$X$'

                  15                                    , dbms_assert.simple_sql_name(x.c2)

                  16                                    );

                  17      else

                  18        l_query := l_query ||replace( q'|, max(decode(c2,'$X$',c3)) $X$|', '$X$'

                  19                                    , dbms_assert.simple_sql_name(x.c2)

                  20                                    );

                  21      end if;

                  22    end loop;

                  23    --

                  24    l_query := l_query || ' from t_temp order by c1';

                  25    --

                  26    dbms_output.put_line(l_query);

                  27    open p_cursor for l_query;

                  28    --

                  29  end;

                  30  /

                   

                  Procedure created.

                   

                  SQL> var rc refcursor

                  SQL> set autoprint on

                  SQL> exec my_pivot(:rc);

                  select  max(decode(c2,'a',c3)) a, max(decode(c2,'b',c3)) b, max(decode(c2,'c',c3)) c from t_temp order by c1

                   

                  PL/SQL procedure successfully completed.

                   

                   

                           A          B          C

                  ---------- ---------- ----------

                           1          2          3

                   

                  1 row selected.

                   

                  Borrowed the example from: Ask Tom "Dynamic Pivot"

                  • 7. Re: Convert row into column
                    user10412263

                     

                    SELECT

                     

                    * FROM (select col2,col3from t_temp)

                     

                     

                    pivot

                     

                    (

                     

                    MAX(col3) for (col2) in ('a','b','c')