how to transform from rows to columns(zt from asktom)

G -- Thanks for the question regarding "Rows into columns", version 9.0.1
originally submitted on 24-Jan-2004 20:20 Eastern US time, last updated 13-Apr-2005 9:55Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)

Hi Tom,

I have a query

sql> SELECT tr_date, item_id, adult_price adult, child_price child
FROM mytable
WHERE tr_date BETWEEN '01-jan-2004' AND '31-jan-2004'
AND   tr_code LIKE 'D%'

The output is

TR_DATE            ITEM_ID    ADULT    CHILD
06/01/2004    8    1189    832.3
06/01/2004    9    1199    839.3
06/01/2004    588    1249    874.3
06/01/2004    589    1239    867.3
06/01/2004    1625    2389    1672.3
06/01/2004    2186    2439    1707.3
06/01/2004    4081    2099    1469.3
06/01/2004    4083    2149    1504.3
.......
.......
.......
.......
.......
.......
.......

Now I want output like this (distinct dates in vertical and distinct item_id in
horizontal) and then adult and child price in cells. The number of columns
(item_id) depend on number of distinct item_id's returned by query and number of
rows depend on number of distinct tr_date returned by query. The adult and child
depend on the adult and child price for that date and that item_id. Hence I want
the result like this

Date/Itin_id    8    8    9    9    588    588    589
          adult    child    adult    child    adult    child    adult
06/01/2004    1189    832.3    1199    839.3    1249    874.3    1239
07/01/2004    ….    ….    ….    ….    ….    ….    ….
08/01/2004    ….    ….    ….    ….    ….    ….    ….
10/01/2004    ….    ….    ….    ….    ….    ….    ….
12/01/2004    ….    ….    ….    ….    ….    ….    ….
13/01/2004    ….    ….    ….    ….    ….    ….    ….

Please advise.

Regards,

GS
 

 
and we said...

create or replace package pivot
as
   type rc is ref cursor;
   procedure data ( p_cursor in out rc );
end;
/
create or replace package body pivot
as
                                                                                
      
procedure data( p_cursor in out rc )
is
    l_stmt long;
begin
                                                                                
      
    l_stmt := 'select tr_date';
    for x in ( select distinct item_id from t order by 1 )
    loop
        l_stmt := l_stmt ||
        ', max(decode(item_id,' || x.item_id ||
             ', adult )) adult_' || x.item_id ||
        ', max(decode(item_id,' || x.item_id ||
             ', child )) child_' || x.item_id;
    end loop;
    l_stmt := l_stmt || ' from t group by tr_date order by tr_date';
                                                                                
      
    open p_cursor for l_stmt;
end;
                                                                                
      
end;
/
                                                                                
      
                                                                                
      
variable x refcursor
set autoprint on
exec pivot.data( :x );

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-796037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-796037/