Forum Stats

  • 3,873,340 Users
  • 2,266,537 Discussions
  • 7,911,515 Comments

Discussions

Transpose rows into columns

shrnxt
shrnxt Member Posts: 22
edited Jun 27, 2016 8:53AM in SQL & PL/SQL

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

Tagged:
shrnxtKalpataru

Best Answer

  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    edited Jun 26, 2016 10:05PM 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):

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

«1

Answers

This discussion has been closed.