Re: how do I combine 24 rows(3 columns) into one row (

From: Yuri McPhedran <esiyuri_at_my-dejanews.com>
Date: Fri, 19 Feb 1999 09:50:40 GMT
Message-ID: <7ajc5e$77d$1@nnrp1.dejanews.com>

In article <7X%y2.51694$O54.62998_at_newscene.newscene.com>,   jenny_at_aol.com (jennfier) wrote:
> i have a table that has 24 rows
>
> name date hour
> joe 2/1/99 1
> joe 2/1/99 21
> joe 2/1/99 32
> joe 2/1/99 34
> ..
> joe 2/1/99 14
>
> i want then to go into a row like this
>
> name date hour1 hour2 hour3 hour4 ... hour24
> joe 2/1/99 1 21 32 34 14
>
> can somebody give the sql please!

Jenny,

A solution to this requires a number of assumptions.

Firstly I shall assume that your table has an extra column (num) that shows which hour a record relates to, otherwise the answer gets more complicated. You need to have a way of telling which record relates to which hour. You can not rely on the order in which the records come back in your select statement!

Perhaps your 'date' column contains a time too, but I shall assume that it doesn't. You also don't give the name of your table, so I shall use "mytab", and I shall assume that it's primary key is (name, date, num).

try (something like) this...

select t1.name, t1.date,
 t1.hour hour1,
 t2.hour hour2,
 t3.hour hour3,

 :
 :
 t23.hour hour23,
 t24.hour hour24
from

 mytab t1,
 mytab t2,
 mytab t3,

 :
 :
 mytab t23,
 mytab t24
where t1.name = 'joe'

and t1.date = to_date('02-JAN-1999','DD-MON-YYYY')
and t1.num = 1
and t2.name = t1.name
and t2.date = t1.date
and t2.num = 2
and t3.name = t1.name
and t3.date = t1.date
and t3.num = 3

:
:

and t23.name = t1.name
and t23.date = t1.date
and t23.num = 23
and t24.name = t1.name
and t24.date = t1.date
and t24.num = 24;

I hope this helps.

Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Feb 19 1999 - 03:50:40 CST