Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to transpose columns and rows in 9i SQL*Plus

Re: How to transpose columns and rows in 9i SQL*Plus

From: Peter Robson <peter.robson_at_gmail.com>
Date: Fri, 10 Nov 2006 21:30:38 +0000
Message-ID: <241f7000611101330p59a1d276h69515f7fe7369d2d@mail.gmail.com>


Without providing you with a bespoke solution (sorry!), suffice to say yes, it can be done. This is basically a cross tabulation problem (or pivot table, in M'Soft speak). Decode could be used. I suggest you Google search on 'sql cross tabulation'. Within the first few hits I found this:

http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

Which will go some way to explaining how to do it. As that writer says, it is complex, but certainly not dauntingly so.

Good luck,

peter
edinburgh
..............

On 11/10/06, Tom Pall <oracle.list_at_gmail.com> wrote:
> I remember seeing the answer to this question years ago. I was sure I saved
> off the answer but I can't find it in my files.
>
> I have this query:
>
>
> select to_char(trunc(TSTAMP),'YYYY-MM-DD') as Day,
> substr(ID,4,6) as value1,count(unique TSTAMP) as hour
> from Mytable
> where substr(ID,4,6) in
> (select unique substr(ID,4,6)
> from Mytable)
> and TSTAMP between TO_DATE ('31-Oct-2006 00:00:00', 'dd-mon-yyyy
> HH24:mi:ss') and
> TO_DATE('09-Nov-2006 23:59:59','dd-mon-yyyy HH24:mi:ss')
> group by
> substr(ID,4,6),to_char(trunc(TSTAMP),'YYYY-MM-DD');
>
>
>
> DAY VALUE1 HOUR
>
> ------------- ----- ------ --------
>
> 2006-10-31 49890 18
>
> 2006-11-01 49890 20
>
> 2006-11-02 49890 20
>
> 2006-11-03 49890 24
>
> 2006-11-04 49890 9
>
> 2006-11-05 49890 8
>
> 2006-11-06 49890 24
>
> 2006-11-07 49890 24
>
> 2006-11-08 49890 18
>
> 2006-11-09 49890 6
>
> 2006-10-31 707112 18
>
>
> to display as
>
>
>
> Value1 11/01 11/02 11/03 11/04
>
> 49890 23 24 24 24
>
> 707112 24 24 24 24
>
>
>
> It's the column/row swap. Using a decode? Oh, I'm on 9.2 with ths query.
>
>
>
>
>
> Could someone kindly help? I haven't done this sort of thing in ages.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 10 2006 - 15:30:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US