Transpose rows into columns

shrnxt

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...

Barbara Boehmer

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):

[email protected]_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.

Stew Ashton

What version of the Oracle database are you running?

shrnxt

Oracle 11g

Jiri.Machotka-Oracle

Then, take a look at the PILOT operator

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

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 :

Can someone help me with the query.