How to transpose columns and rows in 9i SQL*Plus
- From: "Tom Pall" <oracle.list@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Nov 2006 14:36:08 -0600
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.
Other related posts:
