Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Transpose rows into columns

shrnxtJun 25 2016 — edited Jun 27 2016

I have a table like:

Client     Process       Date                   Status

A              B                 21-june             Y

C             D                 22-june             N

A             B                 22-june             N

C            D                 21-june              Y

..

..

n rows

I want to display a report in the following format:

Client     Process        21-June         22-june        .. .... n colums

A               B                 Y                     N

C              D                    Y                     N

Please help...

This post has been answered by Barbara Boehmer on Jun 26 2016
Jump to Answer

Comments

Stew Ashton

What version of the Oracle database are you running?

shrnxt

Oracle 11g

Saubhik

shrnxt

select * from table (pivot  ('select process_status

                      from  sla_status

                      group by  process_name, process_date'))

I tried the above query but to no avail.

Kalpataru

Hi shrnxt,

There are lot's of thread transpose rows into columns.

Check this may help you.

You can also search on forum.

Post your create table and insert statement script with your expected sample output you want to display.

Pavan Kumar
SQL> with data as      

(

select  'A' as Client, 'B' as Process, '21-june' as Date1, 'Y' as Status from dual union all

select  'C' as Client, 'D' as Process, '22-june' as Date1, 'N' as Status from dual union all

select  'A' as Client, 'B' as Process, '22-june' as Date1, 'N' as Status from dual union all

select  'C' as Client, 'D' as Process, '21-june' as Date1, 'Y' as Status from dual

)

select *

from data

pivot(  max(status) for Date1 in ('21-june' as "21-june", '22-june' as "22-june"))  2 3 4 5 6 7 8 9   10  ;

C P 2 2

- - - -

A B Y N

C D Y N

Hope it helps

- Pavan Kumar N

shrnxt

Hi

I checked the articles, but none deal with dates.

Also I wish to display status and not averages or sum

shrnxt

Hi Praveen

Thanks for the reply.

Process status is string

I tried the following query but to no avail:

select client_operator, process_name,process_date, process_status from table

(pivot(max(status) for process_date in (select process_date from table)))

shrnxt

Hi

I was able to get the ouput in Oracle Apex 5 by using pivot feature of interactive report.

I am attaching the screenshot : pastedImage_1.png

Can someone help me with the query.

Pavan Kumar

Hi,

1. Please provide the required inputs statements --- since as per your earlier example I have provided the output

(pivot(max(status) for process_date in (select process_date from table)))

2. You need to alias it

- Pavan Kumar N

shrnxt
CREATE TABLE  "SLA_STATUS" 
   (     "CLIENT_OPERATOR" VARCHAR2(100),
     "PROCESS_NAME" VARCHAR2(100),
     "PROCESS_TICK" VARCHAR2(20),
     "PROCESS_DATE" DATE
   )
/

Insert into sla_status values ('A','B','Y','06/21/2016');

Insert into sla_status values ('C',D','N','06/22/2016');

Insert into sla_status values ('A','B','Y','06/22/2016');

Insert into sla_status values ('C','D',N','06/21/2016');

Pavan Kumar

SQL> select *

from (select * from sla_status)

pivot(  max(PROCESS_TICK) for PROCESS_DATE in (to_date('06/21/2016','MM/DD/YYYY'), to_date('06/22/2016','MM/DD/YYYY')))  2    3  ;

CLIENT_OPE PROCESS_NA TO_DATE('06/21/2016' TO_DATE('06/22/2016'

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

A       B          Y            Y

C       D          N            N

shrnxt

Thanks Pawan

I will give it a try tommorrow.

I dont need hardcoded dates. Will processdate between sysdate and sysdate-7 work?

Pavan Kumar

Probably you need to get it in dynamic way -- you need to use plsql @"BluShadow" demoed it

Refer to that link

- Pavan Kumar N

shrnxt

Thanks Pawan

This query works, but I want it to be dynamic.

Can you please send me the link or an alternative way.

Barbara Boehmer

The following dynamically generates the query, displays the generated query, then displays the results of the query.

SCOTT@orcl_12.1.0.2.0> select * from sla_status

  2  /

CLIENT_OPERATOR PROCESS_NAME    PROCESS_TICK        PROCESS_DA

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

A              B              Y                    06/21/2016

C              D              N                    06/22/2016

A              B              Y                    06/22/2016

C              D              N                    06/21/2016

4 rows selected.

SCOTT@orcl_12.1.0.2.0> variable g_ref refcursor

SCOTT@orcl_12.1.0.2.0> declare

  2    v_sql  clob;

  3  begin

  4    v_sql := 'select * from sla_status pivot(max(process_tick) for process_date in (';

  5    for r in (select sysdate - (8 - rownum + 1) dateval from dual connect by level <= 8) loop

  6      v_sql := v_sql || '''' || r.dateval || ''' as "' || r.dateval || '",';

  7    end loop;

  8    v_sql := rtrim (v_sql, ',') || '))';

  9    dbms_output.put_line (v_sql);

10    open :g_ref for v_sql;

11  end;

12  /

select * from sla_status pivot(max(process_tick) for process_date in ('06/17/2016' as "06/17/2016",'06/18/2016' as "06/18/2016",'06/19/2016' as "06/19/2016",'06/20/2016' as "06/20/2016",'06/21/2016'

as "06/21/2016",'06/22/2016' as "06/22/2016",'06/23/2016' as "06/23/2016",'06/24/2016' as "06/24/2016"))

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> print g_ref

CLIENT_OPERATOR PROCESS_NAME    06/17/2016          06/18/2016          06/19/2016          06/20/2016          06/21/2016          06/22/2016          06/23/2016          06/24/2016

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

A              B                                                                                                  Y                    Y

C              D                                                                                                  N                    N

2 rows selected.

shrnxt

Thanks Babara..

Barbara Boehmer
Answer

Here is another option that enables you to return a result set from SQL using a pipelined function:

Download Anton Scheffer's dynamic pivot function from https://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip

Unzip it and run pivotfun.sql to create the funciton.

I edited the function to change the name from pivot to pivotfun to avoid conflict with an existing Oracle name

and commented out the dbms_output lines from the object before running it.

When you use it, it requires that you provide an alias for the aggregate in the query that you pass to it.

Then you can do the following (note that I used mpt as an alias for the aggregate):

SCOTT@orcl_12.1.0.2.0> select *

  2  from  table (pivotfun (

  3          'select client_operator, process_name, process_date, max (process_tick) mpt

  4          from  (select client_operator, process_name, process_tick, process_date

  5              from  sla_status

  6              union all

  7              select client_operator, process_name, to_char (null) process_tick, process_date

  8              from  (select distinct client_operator, process_name

  9                  from  sla_status),

10                  (select trunc (sysdate) - (8 - rownum + 1) process_date

11                  from  dual connect by level <= 8))

12          group  by client_operator, process_name, process_date' ) )

13  order  by client_operator, process_name

14  /

CLIENT_OPERATOR PROCESS_NAME    06/18/2016          06/19/2016          06/20/2016          06/21/2016          06/22/2016          06/23/2016          06/24/2016          06/25/2016

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

A              B                                                                              Y                    Y

C              D                                                                              N                    N

2 rows selected.

Marked as Answer by shrnxt · Sep 27 2020
shrnxt

Thanks Barbara for helping me out.

This was exactly what I was looking for.

Thanks once again.

1 - 20
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 25 2016
Added on Jun 25 2016
20 comments
4,460 views