Solved

Oracle pivot,

Posted on 2014-10-02
6
373 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle RMAN - trying to duplicate a database 5 52
Oracle Insert not working 10 48
oracle date format checking 7 33
Oracle 12c Default Isolation Level 17 56
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 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