Pivot Query - Columns to Rows

[Exie]

Hi,

Before anyone jumps up and down about not trying to search for an answer, I HAVE!

I found alot of talk about going from multiple rows into a single row with multiple columns, but cant find anything about going the other way.

I basically have a wide table with 12 columns (JAN, FEB, MAR etc) like so:
X 100 150 200 80 ...

... and what I want to retrieve into 12 rows and fob a date like:
X '01JAN09' 100
X '01FEB09' 150
X '01MAR09' 200
etc.

I've tried using a UNION ALL ...but thats just so messy, I'll end up with 200 line SQL query! Surely theres a better way ?

 

[SantaMufasa]

Exie,

Perhaps the simplest final code to achieve your objective relies upon my PARSE function. (By final, I mean "once you have the PARSE function built". I believe that you will find that the PARSE function will come in very handy whenever you are dealing with data that disobeys First Normal Form (1NF).)

Here is the code for the PARSE function:

create or replace function parse (str_in varchar2, which number, delimiter varchar2)
    return varchar2
is
    delim_loc       number;
    prev_beg        number;
    ret_str         varchar2(4000);
begin
    if which < 1 then
        return null;
    end if;
    delim_loc := instr(str_in,delimiter,1,which);
    if delim_loc = 0 then
        delim_loc := length(str_in)+1;
    end if;
    if which = 1 then
        prev_beg := 1;
    else
        prev_beg := instr(str_in,delimiter,1,which-1)+1;
    end if;
    ret_str := substr(str_in,prev_beg,delim_loc-prev_beg);
    if length(ret_str) = length(str_in) then
        if which = 1 then
            return ret_str;
        else
            return null;
        end if;
    else
        return ret_str;
    end if;
end;
/

Function created.

Notice that the three arguments to pass to PARSE are:[ul][li]the input string to parse,[/li][li]the occurrence within the input string to return[/li][li]the delimiter to use to separate values within the input string.[/li][/ul]Here are some sample data:

select * from exie
/

ID   JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
-- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
X    100   150   200   250   300   350   400   450   500   550   600   650
Y   1100  1150  1200  1250  1300  1350  1400  1450  1500  1550  1600  1650

2 rows selected.

Here is simple code that displays what you wanted, using the PARSE function:

col Amt format a5
select id
      ,add_months(trunc(sysdate,'YYYY'),cnt-1) "Date"
      ,parse(JAN||','||FEB||','||MAR||','||APR||','||MAY||','||JUN||','||
             JUL||','||AUG||','||SEP||','||OCT||','||NOV||','||DEC,cnt,',') Amt
  from exie
      ,(select level cnt from dual connect by level <= 12)
 order by id,cnt
/

ID Date      AMT
-- --------- -----
X  01-JAN-09 100
X  01-FEB-09 150
X  01-MAR-09 200
X  01-APR-09 250
X  01-MAY-09 300
X  01-JUN-09 350
X  01-JUL-09 400
X  01-AUG-09 450
X  01-SEP-09 500
X  01-OCT-09 550
X  01-NOV-09 600
X  01-DEC-09 650
Y  01-JAN-09 1100
Y  01-FEB-09 1150
Y  01-MAR-09 1200
Y  01-APR-09 1250
Y  01-MAY-09 1300
Y  01-JUN-09 1350
Y  01-JUL-09 1400
Y  01-AUG-09 1450
Y  01-SEP-09 1500
Y  01-OCT-09 1550
Y  01-NOV-09 1600
Y  01-DEC-09 1650

24 rows selected.

Notice that my method includes:[ul][li]Concatenating the 1NF-disobedient data into a single string, separated with any delimiter (in my case, I chose a comma, ",").[/li][li]Joining with the dual table, which I forced (using "connect by level <= 12") to return 12 rows, to generate 12 months of output for each 1 row of input.[/li][li]Using the PARSE function to separate out individual values for each row of output.[/li][/ul]Let us know how you like this method, or if you have questions about the query, the PARSE function, or their behaviors.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”