asked on

Query to place data in columns by date/time from rows

I have data as such

datetime ID Value
04.09.2007 14:00      23      1.2
04.09.2007 14:00      27      4.8
04.09.2007 14:00      30      9.2
04.09.2007 15:00      23      1.3
04.09.2007 15:00      27      4.9
04.09.2007 15:00      30      9.1

I would like to query out (display like this):

datetime 23 27 30
04.09.2007 14:00      1.2      4.8      9.2
04.09.2007 15:00      1.3      4.9      9.1

What query can do this?

Guy Hengel [angelIII / a3]

this is called CROSSTAB / PIVOT:
http://www.blueclaw-db.com/accessquerysql/pivot_query.htm

ASKER CERTIFIED SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robjay

ASKER

Thanks Mohan - can't get that to work in an Oracle SQL session

Guy Hengel [angelIII / a3]

what error do you get?

imaredia

The query given by Mohan works perfect for me. Here is what i did, let me know if it's still a problem.

CREATE TABLE t
( id number,
datetime date,
VALUE number(4,2)
)

INSERT INTO t(id, datetime, value) SELECT 23, to_date('04.09.2007 14:00', 'DD.MM.YYYY hh24:mi'), 1.2 FROM dual;

INSERT INTO t(id, datetime, value) SELECT 27, to_date('04.09.2007 14:00', 'DD.MM.YYYY hh24:mi'), 4.8 FROM dual;

INSERT INTO t(id, datetime, value) SELECT 30, to_date('04.09.2007 14:00', 'DD.MM.YYYY hh24:mi'), 9.2 FROM dual;

INSERT INTO t(id, datetime, value) SELECT 23, to_date('04.09.2007 15:00', 'DD.MM.YYYY hh24:mi'), 1.3 FROM dual;

INSERT INTO t(id, datetime, value) SELECT 27, to_date('04.09.2007 15:00', 'DD.MM.YYYY hh24:mi'), 4.9 FROM dual;

INSERT INTO t(id, datetime, value) SELECT 30, to_date('04.09.2007 15:00', 'DD.MM.YYYY hh24:mi'), 9.1 FROM dual;

COMMIT;

SELECT datetime, MAX (DECODE (ID, 23, VALUE, NULL)) "23",
MAX (DECODE (ID, 27, VALUE, NULL)) "27",
MAX (DECODE (ID, 30, VALUE, NULL)) "30"
FROM t
GROUP BY datetime;