Solved

Oracle pivot,

Posted on 2014-10-02
6
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 77

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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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