help optimizing transpose query

[erbrose]

Hello All!
Hoping some folks could help me optimize and or choose the best route to do this process.
First off, here is what I am trying to achieve. I have a (fairly large) table of ~34million rows that looks something like this.

ID,TIME,SPD
1,1,35
1,2,25
1,3,34
1,4,25
2,1,25
2,2,11
2,3,56
3,2,22
3,3,25
3,4,22

I am trying to transpose the table by ID into a new table that looks like this (although there will be 96 time fields)

ID,TIME1,TIME2, TIME3,TIME4
1,35,25,34,25
2,25,11,56,null
3,null,22,25,22

This was the original query I used (which worked fine when the table was 1.4 million rows, but now doesn't... it runs out of tempspace (even at 100mb tempspace size)

CREATE TABLE trans_data parallel 8 AS
SELECT /*+ parallel (l,8) */
  l.ID,
  tbl_TIME1.TIME1,tbl_TIME2.TIME2,tbl_TIME3.TIME3,tbl_TIME4.TIME4
FROM org_data l 
  LEFT JOIN
    (
    SELECT
      ID,
      SPD AS TIME1
    FROM
      org_data l
    WHERE
      TIME=1
    )
     tbl_TIME1
  ON
    l.ID=tbl_TIME1.ID
  LEFT JOIN
    (
    SELECT
      ID,
      SPD AS TIME2
    FROM
      org_data l
    WHERE
      TIME=2
    )
     tbl_TIME2
  ON
    l.ID=tbl_TIME2.ID
  LEFT JOIN
    (
    SELECT
      ID,
      SPD AS TIME3
    FROM
      org_data l
    WHERE
      TIME=3
    )
     tbl_TIME3
  ON
    l.ID=tbl_TIME3.ID
  LEFT JOIN
    (
    SELECT
      ID,
      SPD AS TIME4
    FROM
      org_data l
    WHERE
      TIME=4
    )
     tbl_TIME4
  ON
    l.ID=tbl_TIME4.ID;

this code worked about the same as far as speed little bit faster.. but again on the new larger table it runs out of tempspace.

CREATE TABLE CREATE TABLE trans_data parallel 8 AS
SELECT /*+ parallel (l,8) */
a.tmc,
MAX(CASE WHEN a.TIME=1 THEN a.AVG END) AS TIME1,
MAX(CASE WHEN a.TIME=2 THEN a.AVG END) AS TIME2,
MAX(CASE WHEN a.TIME=3 THEN a.AVG END) AS TIME3,
MAX(CASE WHEN a.TIME=4 THEN a.AVG END) AS TIME4
FROM
org_data a
GROUP BY
a.ID;

and here is my latest attempt
I create the table first, populate it with unique ID's then run this

UPDATE trans_data a SET TIME1 = (SELECT /*+ index (trans_data, ID_INDEX) */ AVG FROM orig_data b WHERE TIME=1 and a.ID=b.ID);
COMMIT;

this works but takes about 8-10 minutes per column
which at 96 columns runs to over 8 hours, trying to shorten up the amount of time taken (if possible!)
Sorry for posting such a long post here, but if anyone can help, I will forever be indebted!
Cheers,
Eric

[Dave44]

I like the second approach best personally, creating the pivot table seems is good. Is there no way to restrict on the rows? I mean why do you need to keep redoing the rows you did before? (there is no where clause in the query so your redoing the entire table!)

if you have built a new table from the old can you not add some kind of timestamp to it so you know what you have done already?

[erbrose]

Thanks for the reply Dave44
was a bit frustrated yesterday and not thinking as clearly as I should have been, your post gave me the much needed inspiration ie adding a where statement.
so just broke up the case statements into 4 temp tables by adding a where time between # and #
and then did a left join of the four temp tables at the end. It ran in less then 5 minutes...
Thanks
Eric