6 Replies Latest reply: Feb 28, 2014 4:22 AM by AnnPricks E RSS

    Rows to Column

    Azamshareef

      Hi All,

       

      I have problem with query and table description and data as follows

       

      name varchar2(100),

      hdate timestamp,

      user varchar2(10)

       

      data

      name  hdate                                user

      xx       01-jan-2014 12:00:54 am    2444

      xx       01-jan-2014 14:00:54 am    2444

      xx       01-jan-2014 13:00:54 am    2444

      xx       01-dec-2013 12:00:54 am    2444

      xx       01-jan-2014  16:00:54 am     2444

       

       

      I want to display data as follows.

       

      name    01-jan-2014      01-dec-2014

      xx        12:00:54 am     12:00:54 am

      xx         14:00:54 am

      xx        13:00:54 am

      xx        16:00:54 am

       

      i want to display date as column name and time as rows....

       

      Can anyone help me in this regards......

        • 1. Re: Rows to Column
          Hoek

          See this SQL and PL/SQL FAQ: Re: 4. How do I convert rows to columns?

          Also, please post a testcase we can run on our databases, and your database version.

          How to do that is explained here: Re: 2. How do I ask a question on the forums?

          • 2. Re: Rows to Column
            _Karthick_

            May be this?

             

            SQL> with t
              2  as
              3  (
              4  select 'xx' name, to_timestamp('01-jan-2014 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr
              5    from dual union all
              6  select 'xx' name, to_timestamp('01-jan-2014 14:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr
              7    from dual union all
              8  select 'xx' name, to_timestamp('01-jan-2014 13:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr
              9    from dual union all
            10  select 'xx' name, to_timestamp('01-dec-2013 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr
            11    from dual union all
            12  select 'xx' name, to_timestamp('01-jan-2014 16:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr
            13    from dual
            14  )
            15  select name
            16       , max(decode(trunc(hdate), to_timestamp('01-jan-2014', 'dd-mon-yyyy'), hdate-trunc(hdate))) "01-JAN-2014"
            17       , max(decode(trunc(hdate), to_timestamp('01-dec-2013', 'dd-mon-yyyy'), hdate-trunc(hdate))) "01-DEC-2013"
            18    from (
            19            select name
            20                 , hdate
            21                 , row_number() over(partition by name, trunc(hdate) order by hdate) rno
            22              from t
            23         )
            24   group
            25      by name
            26       , rno
            27   order
            28     by name
            29      , rno;

             

            NA 01-JAN-2014                     01-DEC-2013
            -- ------------------------------- -------------------------------
            xx +000000000 12:00:54.000000000   +000000000 12:00:54.000000000
            xx +000000000 13:00:54.000000000
            xx +000000000 14:00:54.000000000
            xx +000000000 16:00:54.000000000

             

            SQL>

            • 3. Re: Rows to Column
              Partha Sarathy S

              You can use PIVOT. Like this.

               

              SELECT NAME,

                     "01-JAN-2014",

                     "01-DEC-2013"

              FROM (

              SELECT NAME,

                    TO_CHAR(HDATE,'DD-MON-YYYY') HDATE,

                    TO_CHAR(HDATE,'HH24:MI:SS') HTIME,

                    USERS,

                    ROW_NUMBER()OVER(PARTITION BY NAME,TRUNC(HDATE) ORDER BY HDATE) RN

              FROM T1)

              PIVOT (MIN(HTIME) FOR (HDATE) IN ('01-JAN-2014' AS "01-JAN-2014",'01-DEC-2013' AS "01-DEC-2013"))

              ORDER BY RN;

              For sample data,

               

              WITH T1 (NAME,HDATE,USERS) AS (

              SELECT 'xx',TO_DATE('01-jan-2014 12:00:54','DD-MON-YYYY HH24:MI:SS'),2444 FROM DUAL UNION ALL

              SELECT 'xx',TO_DATE('01-jan-2014 14:00:54','DD-MON-YYYY HH24:MI:SS'),2444 FROM DUAL UNION ALL

              SELECT 'xx',TO_DATE('01-jan-2014 13:00:54','DD-MON-YYYY HH24:MI:SS'),2444 FROM DUAL UNION ALL

              SELECT 'xx',TO_DATE('01-dec-2013 12:00:54','DD-MON-YYYY HH24:MI:SS'),2444 FROM DUAL UNION ALL

              SELECT 'xx',TO_DATE('01-jan-2014 16:00:54','DD-MON-YYYY HH24:MI:SS'),2444 FROM DUAL)

              SELECT NAME,

                     "01-JAN-2014",

                     "01-DEC-2013"

              FROM (

              SELECT NAME,

                    TO_CHAR(HDATE,'DD-MON-YYYY') HDATE,

                    TO_CHAR(HDATE,'HH24:MI:SS') HTIME,

                    USERS,

                    ROW_NUMBER()OVER(PARTITION BY NAME,TRUNC(HDATE) ORDER BY HDATE) RN

              FROM T1)

              PIVOT (MIN(HTIME) FOR (HDATE) IN ('01-JAN-2014' AS "01-JAN-2014",'01-DEC-2013' AS "01-DEC-2013"))

              ORDER BY RN;

               

              OUTPUT:

               

              NA      01-JAN-2 01-DEC-2

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

              xx      12:00:54 12:00:54

              xx      13:00:54

              xx      14:00:54

              xx      16:00:54

               

               

              Message was edited by: Parth272025

              • 4. Re: Rows to Column
                Nimish Garg

                try this

                 

                select nvl(a.name,b.name) name , "01-JAN-2014", "01-DEC-2013"

                from

                (

                  select rownum rn, x.* from

                  (

                    select name,

                    case when trunc(hdate) = to_date('01-jan-2014', 'dd-mon-yyyy') then to_char(hdate,'hh:mi:ss am') end "01-JAN-2014"

                    from t

                  ) x

                  where "01-JAN-2014" is not null

                ) a

                full outer join

                (

                  select rownum rn, x.* from

                  (

                    select name,

                    case when trunc(hdate) = to_date('01-dec-2013', 'dd-mon-yyyy') then to_char(hdate,'hh:mi:ss am') end "01-DEC-2013"

                    from t

                    order by 2

                  ) x

                  where "01-DEC-2013" is not null

                ) b

                on (a.rn=b.rn)

                • 5. Re: Rows to Column
                  AnnPricks E

                  Hi Try this?

                  SELECT name,

                         "01-JAN-2014",

                         "01-DEC-2013"

                  FROM

                  (SELECT *

                  FROM

                  (SELECT name,

                          TO_DATE(TO_CHAR(hdate,'DD-MON-YYYY')) date_val,

                          TO_CHAR(hdate,'HH24:MI:SS') time_val,

                            usr,

                          ROW_NUMBER() OVER(PARTITION BY name,usr,TRUNC(hdate) ORDER BY hdate) rn

                  FROM t)

                  PIVOT(MAX(time_val) FOR date_val IN(TO_DATE('01-JAN-2014','DD-MON-YYYY') AS "01-JAN-2014",TO_DATE('01-DEC-2013','DD-MON-YYYY') AS "01-DEC-2013")))

                  ORDER BY rn;

                  Testcases:-

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

                  with t

                  as

                  (

                  select 'xx' name, to_timestamp('01-jan-2014 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                     from dual union all

                  select 'xx' name, to_timestamp('01-jan-2014 14:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                     from dual union all

                  select 'xx' name, to_timestamp('01-jan-2014 13:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                     from dual union all

                  select 'xx' name, to_timestamp('01-dec-2013 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                    from dual union all

                  select 'xx' name, to_timestamp('01-jan-2014 16:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                    from dual

                  )

                  SELECT name,

                         "01-JAN-2014",

                         "01-DEC-2013"

                  FROM

                  (SELECT *

                  FROM

                  (SELECT name,

                          TO_DATE(TO_CHAR(hdate,'DD-MON-YYYY')) date_val,

                          TO_CHAR(hdate,'HH24:MI:SS') time_val,

                            usr,

                          ROW_NUMBER() OVER(PARTITION BY name,usr,TRUNC(hdate) ORDER BY hdate) rn

                  FROM t)

                  PIVOT(MAX(time_val) FOR date_val IN(TO_DATE('01-JAN-2014','DD-MON-YYYY') AS "01-JAN-2014",TO_DATE('01-DEC-2013','DD-MON-YYYY') AS "01-DEC-2013")))

                  ORDER BY rn;


                  OUTPUT:-

                  --------

                  NAME 01-JAN-2014 01-DEC-2013

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

                  xx   12:00:54    12:00:54

                  xx   13:00:54

                  xx   14:00:54

                  xx   16:00:54

                   

                  • 6. Re: Rows to Column
                    AnnPricks E

                    Other possible solution..This will work prior 11g

                    SELECT name,

                           MAX(CASE WHEN date_val = TO_DATE('01-JAN-2014','DD-MON-YYYY') THEN time_val END) "01-JAN-2014",

                           MAX(CASE WHEN date_val = TO_DATE('01-DEC-2013','DD-MON-YYYY') THEN time_val END) "01-DEC-2013"

                    FROM

                    (SELECT name,

                            TO_DATE(TO_CHAR(hdate,'DD-MON-YYYY')) date_val,

                            TO_CHAR(hdate,'HH24:MI:SS') time_val,

                            usr,

                            ROW_NUMBER() OVER(PARTITION BY name,usr,TRUNC(hdate) ORDER BY hdate) rn

                    FROM t)

                    GROUP BY name,usr,rn

                    ORDER BY rn;

                    Testcases:-

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

                    with t

                    as

                    (

                    select 'xx' name, to_timestamp('01-jan-2014 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                       from dual union all

                    select 'xx' name, to_timestamp('01-jan-2014 14:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                       from dual union all

                    select 'xx' name, to_timestamp('01-jan-2014 13:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                       from dual union all

                    select 'xx' name, to_timestamp('01-dec-2013 12:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                      from dual union all

                    select 'xx' name, to_timestamp('01-jan-2014 16:00:54', 'dd-mon-yyyy hh24:mi:ss') hdate, 2444 usr

                      from dual

                    )

                    SELECT name,

                           MAX(CASE WHEN date_val = TO_DATE('01-JAN-2014','DD-MON-YYYY') THEN time_val END) "01-JAN-2014",

                           MAX(CASE WHEN date_val = TO_DATE('01-DEC-2013','DD-MON-YYYY') THEN time_val END) "01-DEC-2013"

                    FROM

                    (SELECT name,

                            TO_DATE(TO_CHAR(hdate,'DD-MON-YYYY')) date_val,

                            TO_CHAR(hdate,'HH24:MI:SS') time_val,

                            usr,

                            ROW_NUMBER() OVER(PARTITION BY name,usr,TRUNC(hdate) ORDER BY hdate) rn

                    FROM t)

                    GROUP BY name,usr,rn

                    ORDER BY rn;


                    OUTPUT:-

                    ------

                    NAME   01-JAN-2014 01-DEC-2013

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

                    xx     12:00:54    12:00:54
                    xx     13:00:54
                    xx     14:00:54
                    xx     16:00:54