Unpivot or Connect by [message #655630] |
Tue, 06 September 2016 13:54 |
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 |
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 |
|
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.
|
|
|