Re: how to: crosstable

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: 1997/11/28
Message-ID: <347ed55d.8426526@read.news.global.net.uk>#1/1

On Fri, 28 Nov 1997 13:21:46 +0100, Dietmar Leibecke <dleibeck_at_debis.com.---> wrote:

>Hi there,
>
>I'm looking for some help with a select statement for a crosstable call. The
>data in my table looks like this:
>
>id datetime value flag
>---------------------------------------
>1 1997-11-28 01:00 7.51 X
>1 1997-11-28 02:00 7.55 X
>1 1997-11-28 03:00 7.59 X
>1 1997-11-28 04:00 7.57 X
>...
>2 1997-11-28 01:00 13.31 X
>2 1997-11-28 02:00 13.35 Y
>2 1997-11-28 03:00 13.33 X
>2 1997-11-28 04:00 13.32 Y
>...
>
>Is there a way to create a select statement that returns the following result
>set??
>
>Result:
>
>id day hour_1 flag_1 hour_2 flag_2 hour_3 flag_3 ...
>----------------------------------------------------------------
>1 1997-11-28 7.51 X 7.55 X 7.59 X ...
>2 1997-11-28 13.31 X 13.35 Y 13.33 X ...
>
>
>TIA for any input,
>
>--
>Dietmar Leibecke
>----------------
>Address is altered to discourage junk mail.
>Remove ".---" for the real address.
>
>

Assuming that all your readings are on the hour then the following statement should work, adding extra columns for hours as required.

select id,

        trunc(datetime),
        min(decode(to_char(datetime,'hh24'), '01', value, null ))
hour_1,
        min(decode(to_char(datetime,'hh24'), '01', flag, null ))
flag_1,
        min(decode(to_char(datetime,'hh24'), '02', value, null ))
hour_2,
        min(decode(to_char(datetime,'hh24'), '02', flag, null ))
flag_2,
        min(decode(to_char(datetime,'hh24'), '03', value, null ))
hour_3,
        min(decode(to_char(datetime,'hh24'), '03', flag, null ))
flag_3,
        min(decode(to_char(datetime,'hh24'), '04', value, null ))
hour_4,
        min(decode(to_char(datetime,'hh24'), '04', flag, null ))

flag_4
 from fred
 group by id, trunc(datetime)
 order by id, trunc(datetime) Received on Fri Nov 28 1997 - 00:00:00 CST