Home » SQL & PL/SQL » SQL & PL/SQL » switch data positions
switch data positions [message #666846] Tue, 28 November 2017 12:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #666863 is a reply to message #666849] Wed, 29 November 2017 06:26 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
wtolentino wrote on Tue, 28 November 2017 14:20
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.
then instead of showing some fabricated case, put together a test case using the actual table structure and representative data.
Re: switch data positions [message #666864 is a reply to message #666863] Wed, 29 November 2017 11:06 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
EdStevens wrote on Wed, 29 November 2017 07:26
then 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.
Previous Topic: trigger impacting performance
Next Topic: Function Creation
Goto Forum:
  


Current Time: Thu Mar 28 11:40:27 CDT 2024