Solved

Oracle pivot,

Posted on 2014-10-02
6
363 Views
Last Modified: 2014-10-08
I need to pivot the activity table below into a flat structure. The number of activities is unknown, but all users will have the same number of activities, with the same set of activity Ids.   The activity table has the following structure with example data.

input data
The header / column names are not need but some type of column name is.   There is on group of columns (Activity,start,end,hours) for each activity. So if there are 25 activities there would be 101 columns ((4*25)+1). If there are 50 users there would be 50 rows.

result data

What would be the Oracle SQL to accomplish this ?
0
Comment
Question by:fpkeegan
  • 2
  • 2
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40358366
The maximum number of columns pivoted needs to be fixed or you need some smoke and mirrors.

In other words, you need to know how many columns will be in the result set.

Here is a discussion that talks about some of the 'smoke and mirrors':
https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:4843682300346852395
0
 

Author Comment

by:fpkeegan
ID: 40358488
I have read that article before I posted.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40358491
OK, so what do you want that is different from that article?

You cannot have a dynamic number of columns without the magic posted in there.

Now if you want to have a hard-coded limit of say 100 columns, then we can work with that.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40359746
And who is gong to look at 101 column report?
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 40359921
PS: If you are actually going to export the data, you could spool the result as delimited file:
set lin 4000 pages 0 trims on
spo my_activities.csv
SQL> SELECT User#||LISTAGG (';'|| Activity_Id || ';' || Start_Dt || ';' || End_Dt || ';' || Hours ,' ' )
  2         WITHIN GROUP (ORDER BY Activity_id)  Activity_list
  3      FROM Activity_Tab
  4* GROUP BY User#
SQL> /

1;1;01/20/2013;01/20/2013;14 ;2;01/23/2013;01/25/2013;10 ;3;01/27/2013;01/30/2013;8 ;4;02/10/2013;02/17/2013;17 ;5;02/11/2013;02/15/2013;10 ;6;02/15/2013;02/28/2013;30 ;7;03/15/2013;03/18/2013;6 ;8;03/22/2013;03/29/2013;34 ;9;04/10/2013;04/11/2013;2 ;10;04/21/2013;04/26/2013;20
2;1;06/20/2013;06/20/2013;13 ;2;06/23/2013;06/25/2013;13 ;3;06/27/2013;06/30/2013;10 ;4;07/10/2013;07/17/2013;19 ;5;07/11/2013;07/15/2013;12 ;6;07/15/2013;07/29/2013;31 ;7;09/15/2013;09/18/2013;9 ;8;09/22/2013;09/29/2013;35 ;9;10/10/2013;10/11/2013;4 ;10;10/21/2013;10/26/2013;21

Open in new window

Another option:
SQL> VAR Pivot_Tab REFCURSOR;
SQL>
SQL> DECLARE
  2     Pivot_Query    CLOB;
  3     Col_Grp        CLOB;
  4
  5     TYPE Chr_Typ IS TABLE OF VARCHAR2 ( 30 )
  6        INDEX BY PLS_INTEGER;
  7
  8     Pivot_Col      Chr_Typ;
  9     I              PLS_INTEGER;
 10     J              PLS_INTEGER;
 11     K              PLS_INTEGER;
 12  BEGIN
 13     SELECT DISTINCT Activity_Id
 14       BULK COLLECT INTO Pivot_Col
 15       FROM Activity_Tab
 16      ORDER BY 1;
 17
 18     Pivot_Query := '';
 19     Col_Grp     := '';
 20
 21     FOR I IN 1 .. Pivot_Col.COUNT
 22     LOOP
 23        Col_Grp     := Col_Grp || ', MAX(DECODE(Activity_Id,''' || Pivot_Col ( I ) || ''',Activity_Id)) Activity_Id';
 24        Col_Grp     := Col_Grp || ', MAX(DECODE(Activity_Id,''' || Pivot_Col ( I ) || ''',start_dt)) start_dt';
 25        Col_Grp     := Col_Grp || ', MAX(DECODE(Activity_Id,''' || Pivot_Col ( I ) || ''',end_dt)) end_dt';
 26        Col_Grp     := Col_Grp || ', MAX(DECODE(Activity_Id,''' || Pivot_Col ( I ) || ''',hours)) hours';
 27     END LOOP;
 28
 29     Pivot_Query := 'SELECT User# ' || CHR ( 10 ) || Col_Grp ||
 30                    '  FROM Activity_Tab  GROUP BY User# ';
 31     DBMS_OUTPUT.Put_Line ( Pivot_Query );
 32
 33     OPEN :Pivot_Tab FOR Pivot_Query;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL>
SQL> PRINT Pivot_Tab

     USER# ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS ACTIVITY_ID START_DT   END_DT          HOURS
---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
         1           1 01/20/2013 01/20/2013         14           2 01/23/2013 01/25/2013         10           3 01/27/2013 01/30/2013          8           4 02/10/2013 02/17/2013         17           5 02/11/2013 02/15/2013         10           6 02/15/2013 02/28/2013         30           7 03/15/2013 03/18/2013          6           8 03/22/2013 03/29/2013         34           9 04/10/2013 04/11/2013          2          10 04/21/2013 04/26/2013         20
         2           1 06/20/2013 06/20/2013         13           2 06/23/2013 06/25/2013         13           3 06/27/2013 06/30/2013         10           4 07/10/2013 07/17/2013         19           5 07/11/2013 07/15/2013         12           6 07/15/2013 07/29/2013         31           7 09/15/2013 09/18/2013          9           8 09/22/2013 09/29/2013         35           9 10/10/2013 10/11/2013          4          10 10/21/2013 10/26/2013         21

SQL>

Open in new window

0
 

Author Closing Comment

by:fpkeegan
ID: 40369697
Well done. love the code examples,  very easy to follow.

By the way I have reports that have +500 columns, they are used to load into excel and do major project analysis performance form multiple databases, both oracle and SQL.  Some projects have more that 100+ activities.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now