Home » SQL & PL/SQL » SQL & PL/SQL » switch data positions
switch data positions [message #666846] |
Tue, 28 November 2017 12:54 |
wtolentino
Messages: 390 Registered: March 2005
|
Senior Member |
|
|
sample data:
select '2015_CA,2015_CP,2016_CA,2016_CP,2017_CA,2017_CP' col_name,
'168081.55,168082.50,168060.38,168062.30,168072.41,0' col_value
from dual;
SQL> select '2015_CA,2015_CP,2016_CA,2016_CP,2017_CA,2017_CP' col_name,
2 '168081.55,168082.50,168060.38,168062.30,168072.41,0' col_value
3 from dual;
COL_NAME COL_VALUE
----------------------------------------------- ---------------------------------------------------
2015_CA,2015_CP,2016_CA,2016_CP,2017_CA,2017_CP 168081.55,168082.50,168060.38,168062.30,168072.41,0
SQL>
how do i switch the data position in such that it will look like this:
COL_NAME COL_VALUE
----------------------------------------------- ---------------------------------------------------
2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP 168081.55,168060.38,168072.41,168082.50,168062.30,0
SQL>
on the column col_name those that have a string of _CA will come first then followed by strings that have _CP. the column col_value is dependent on the col_name. for example on col_name data 2015_CA it's corresponding data on the col_value is 168081.55. when the position of the col_name is changed the position of col_value also changed.
thank you.
|
|
|
Re: switch data positions [message #666848 is a reply to message #666846] |
Tue, 28 November 2017 13:43 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Table design is flawed. You should normalize it to 3NF. Anyway:
with t as (
select rowid rid,
regexp_substr(col_name,'[^,]+',1,level) col_name,
regexp_substr(col_value,'[^,]+',1,level) col_value,
row_number() over(
partition by rowid
order by regexp_substr(
regexp_substr(col_name,'[^,]+',1,level),
'_.+$'
),
regexp_substr(col_name,'[^,]+',1,level)
) rn
from tbl
connect by rowid = prior rowid
and prior sys_guid() is not null
and level <= regexp_count(col_name,',') + 1
)
select listagg(col_name,',') within group(order by rn) col_name,
listagg(col_value,',') within group(order by rn) col_value
from t
group by rid
/
COL_NAME COL_VALUE
-------------------------------------------------- ---------------------------------------------------
2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP 168081.55,168060.38,168072.41,168082.50,168062.30,0
SQL>
SY.
P.S. You can change hierarchical to lateral or cross-apply join if you are on 12C.
|
|
|
Re: switch data positions [message #666849 is a reply to message #666848] |
Tue, 28 November 2017 14:20 |
wtolentino
Messages: 390 Registered: March 2005
|
Senior Member |
|
|
thanks so much. the data was taken from a normalized table and was been somewhat converted to make it look like as a matrix data. this is partly because the year is not fixed it is based on a begin and end year parameter. the year is unpredictable and can vary from any multiple year range.
[Updated on: Tue, 28 November 2017 14:26] Report message to a moderator
|
|
|
|
Re: switch data positions [message #666864 is a reply to message #666863] |
Wed, 29 November 2017 11:06 |
wtolentino
Messages: 390 Registered: March 2005
|
Senior Member |
|
|
EdStevens wrote on Wed, 29 November 2017 07:26then instead of showing some fabricated case, put together a test case using the actual table structure and representative data.
as much as possible i would like to make it simple so it will be easier to understand. this is why i only posted a subset of data. solomon has given the correct example.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 11:40:27 CDT 2024
|