Re: How to transpose columns and rows in 9i SQL*Plus
- From: "Peter Robson" <peter.robson@xxxxxxxxx>
- To: oracle.list@xxxxxxxxx
- Date: Fri, 10 Nov 2006 21:30:38 +0000
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@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
