Newbie Query Question

I have a table that is "long" and I must transform the table into a "wide" table. For example, the long table is DATE, DATAVALUE, DATANAME (where there is one record for each measured DATAVALUE and where DATANAME comes from pre-defined list of names) and I need to transform this table into DATE, DATANAME1, DATANAME2, DATANAME3, ETC, where I get one record for each unique DATE and where DATAVALUE from the original table is assigned to the corresponding DATANAME column in the "wide" table.

I have done this two ways, and one way is much slower in Oracle than the other. Why? Any suggestions for another method? Both methods seem to run quickly in Access...

Method 1. Something like the following...

SELECT DATE AS DATE,
(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
WHERE DATANAME = 'DATANAME1') AS DATANAME1,
(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
DATANAME = 'DATANAME2') AS DATANAME2,
(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
WHERE DATANAME = 'DATANAME3') AS DATANAME3 FROM tbl1

Method 2. And something like the following

SELECT DATE AS DATE,

SELECT tbl1_tmp1.DATAVALUE AS DATANAME1
SELECT tbl1_tmp2.DATAVALUE AS DATANAME2
SELECT tbl1_tmp3.DATAVALUE AS DATANAME3

FROM
(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
WHERE DATANAME = 'DATANAME1') tbl1_tmp1,

(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
DATANAME = 'DATANAME2') tbl1_tmp2,

(SELECT DATAVALUE

FROM tbl1_tmp
WHERE tbl1.DATE = tbl1_tmp.DATE AND
WHERE DATANAME = 'DATANAME3') tbl1_tmp3,

tbl1

Thanks,

JES Received on Sat Apr 10 2004 - 20:49:17 CDT