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: Luca Canali <Luca.Canali_at_cern.ch>
Date: Mon, 13 Nov 2006 12:33:00 +0100
Message-ID: <F9411E3A291FB5449991ED7E624D27F4017E483A@cernxchg20.cern.ch>


..or you can use a sql*plus replacement instead . With sqlpython
(http://www.oracle.com/technology/community/opensource_projects.html),
for example, you have the extra command 'tselect' to print a transposed result set. It's been developed to ease querying from v$ views (ex: tselect * from v$session where sid=...) but it may fit your scope too.  

Cheers,
L.


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tom Pall Sent: Friday, November 10, 2006 9:36 PM
To: oracle-l
Subject: How to transpose columns and rows in 9i SQL*Plus

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 Mon Nov 13 2006 - 05:33:00 CST

Original text of this message

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