Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle pivot,

Posted on 2014-10-02
6
Medium Priority
?
382 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 78

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 78

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

581 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