Oracle pivot,

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 ?
fpkeeganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
fpkeeganAuthor Commented:
I have read that article before I posted.
0
slightwv (䄆 Netminder) Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeOM_DBACommented:
And who is gong to look at 101 column report?
0
MikeOM_DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fpkeeganAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.