Re: Flipping rows and cols

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 25 Aug 2006 09:45:08 -0700
Message-ID: <1156524307.884142.4790@b28g2000cwb.googlegroups.com>

shabda.raaj_at_gmail.com wrote:
> You might also like to read this.
> http://www.codeproject.com/aspnet/Datatable.asp
> It does the same thing in ASP.
> shabda.r..._at_gmail.com wrote:
> > I need to flip the cols and rows of a table for display purposes. Say
> > my table has three rows and 20 cols I may want to display the data
> > after flipping rows as cols. Can anyone point me to the correct place?

You can do this in plain old SQL.

SQL> select
2 process_date

  3   ,sum(ws.reqstd_amt * pt.a) as "A Dollars"
  4   ,sum(ws.reqstd_amt * pt.e) as "E Dollars"
  5   ,sum(ws.reqstd_amt * pt.i) as "I Dollars"
  6   ,sum(ws.reqstd_amt * pt.k) as "K Dollars"
  7   ,sum(ws.reqstd_amt * pt.v) as "V Dollars"

8 from
9 not_real_name ws
10 ,aeikv_pivot pt
11 where ws.userid_alias = 'DISP TOTAL' 12 and ws.disp_code = pt.value
13 group by process_date
14 /

PROCESS_D A Dollars E Dollars I Dollars K Dollars V Dollars

--------- ---------- ---------- ---------- ---------- ----------
25-JUN-03 1950333.76  357911.44 4375403.45  982284.49   41717.87
26-JUN-03 1724270.12  264826.83  5074678.6  877965.21   41717.87

<snip>

The pivot table conists of X rows with X columns defined. All column values are 0 except for the Nth column on the Nth which are 1 so that the 1's form a diagnal from the upper left to the lower right where all rows are selected.

SQL> select * from aeikv_pivot
2 /

V A E I K V
- - - - - -
A 1 0 0 0 0
E 0 1 0 0 0
I 0 0 1 0 0
K 0 0 0 1 0
V 0 0 0 0 1

Here is a link to a thread with references on the topic which leads to information on other approaches:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/1be7161614a825c1/4967e2c08b611b14?lnk=st&q=&rnum=2&hl=en#4967e2c08b611b14

Analytic queries can be used to do this as well as the connect by clause.

HTH -- Mark D Powell -- Received on Fri Aug 25 2006 - 11:45:08 CDT