Home » SQL & PL/SQL » SQL & PL/SQL » Unpivot or Connect by (Oracle 11.2.0.3 Windows)
Unpivot or Connect by [message #655630] Tue, 06 September 2016 13:54 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Hello,

I'm trying to figure out how to convert the data I have from columns to rows. The columns (M2, F2) should be on a row by themselves with the pledge_number.

CREATE TABLE TEMP_UNPIVOT
(
  PLEDGE_NUMBER  VARCHAR2(30 BYTE),
  M1             NUMBER,
  F1             NUMBER,
  M2             NUMBER,
  F2             NUMBER
)

Insert into TEMP_UNPIVOT
   (PLEDGE_NUMBER, M1, F1, M2, F2)
 Values
   ('99999', 100, 3000, 200, 2000);
COMMIT;



The output I want is:

PLEDGE_NUMBER     M1    F1
99999             100   3000
99999             200   2000



Tried this:

select *
   from temp_unpivot
  unpivot (temp_name for temp_column in (m2, f2))


I'm thinking CONNECT BY but I haven't figured that out yet. Any clues?
Re: Unpivot or Connect by [message #655631 is a reply to message #655630] Tue, 06 September 2016 14:13 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Looks like I got it. I needed more columns in my unpivot statement.


select *
  from (select * from temp_unpivot)
    unpivot((M1x, F1x) for pn in ((M1, F1) as 1,
                                  (M2, F2) as 2))
    order by 1, 2;

Re: Unpivot or Connect by [message #655632 is a reply to message #655631] Tue, 06 September 2016 14:31 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I'm thinking CONNECT BY but I haven't figured that out yet. Any clues?
SQL> with lines as (select level line from dual connect by level <= 2)
  2  select PLEDGE_NUMBER,
  3         decode(line, 1, m1, 2, m2) mx,
  4         decode(line, 1, f1, 2, f2) fx
  5  from lines left outer join temp_unpivot partition by (PLEDGE_NUMBER) on 1=1
  6  /
PLEDGE_NUMBER                          MX         FX
------------------------------ ---------- ----------
99999                                 100       3000
99999                                 200       2000

2 rows selected.
Previous Topic: select statement to split string as per the content and show it
Next Topic: Select columns based on first record (Header from flat file)
Goto Forum:
  


Current Time: Fri Apr 19 17:44:11 CDT 2024