Transpose String colomn values to Row Oracle

Question

my initial table is like this.

CREATE TABLE pivot_string ( col1 NUMBER, col2 NUMBER, col3 VARCHAR2(6), 
 col3_id NUMBER, col3_desc VARCHAR2(10) ) ; 
 INSERT INTO pivot_string SELECT 123, 9875,'RO', 40, 'Roma' FROM dual;
 INSERT INTO pivot_string SELECT 123, 9875,'IT', 40, 'iteration' FROM dual;
 INSERT INTO pivot_string SELECT 123, 9875,'US', 78, 'world' FROM dual;
 INSERT INTO pivot_string SELECT 123, 9875,'WE', 56, 'WHAT' FROM dual;
 COMMIT;
 SELECT * FROM pivot_string;
 -- INSERT INTO pivot_string SELECT 123, 4875,'RO', 55, 'Roma' FROM dual;
 --INSERT INTO pivot_string SELECT 124, 4875,'RO', 44, 'Roma' FROM dual;

 table: pivot_string'
------------------------------------------
col1 |  col2 | col3 | col3_id | col3_desc
-----------------------------------------
123  |  9875 | 'RO' |   40    | 'Roma' 
123  |  9875 | 'IT' |   50    | 'iteration'
123  |  9875 | 'US' |   78    | 'world'
123  |  9875 | 'WE' |   56    | 'WHAT'

Excepted- pivot_string
--------------------------------------------------------------------------
col1 |  col2 | ro | ro_desc | it | it_desc     | us | us_desc | we | we_desc
---------------------------------------------------------------------------
123  |  9875 | 40 |  'Roma' | 50 | 'iteration' | 78 | 'world' | 56 | 'WHAT'

my approach

WITH mytest AS(  
SELECT a.col1, a.col2, a.col3,a.col3_id, a.col3_desc
FROM pivot_string a 
   )
 SELECT coalesce(RO.col1,IT.col1, US.col1, WE.col1)col1,  
   coalesce(RO.col2,IT.col2, US.col2, WE.col2) col2,
   RO,RO_desc IT,IT_desc, US,US_desc, WE, WE_desc
  FROM(

  (SELECT col1,col2, col3_id RO, col3_desc RO_desc FROM mytest WHERE col3 = 
 'RO' )RO
  LEFT JOIN (SELECT col1,col2, col3_id IT, col3_desc IT_desc FROM mytest 
  WHERE col3 = 'IT' )  IT 
       ON(RO.col1 =IT.col1 AND RO.col2 = IT.col2)
   LEFT JOIN ( SELECT col1,col2, col3_id US, col3_desc US_desc FROM mytest 
   WHERE col3 = 'US' )US
      ON(RO.col1 =US.col1 AND RO.col2 = US.col2)
    LEFT JOIN (SELECT col1,col2, col3_id WE, col3_desc WE_desc FROM mytest 
      WHERE col3 = 'WE') WE 
      ON(RO.col1 =WE.col1 AND RO.col2 = WE.col2)
    ) ;

the way i solved this problem is not good because the col3 has more then 100 differents values. so left join all like i do is surely not the right way to do that. I cant use pivot because of col3 und col3_desc. their types are Varchar2

Does someone know a better way to solve this kind of issue?

pOrinG · Accepted Answer · 2017-12-22 14:49:15Z

2

I saw similar question a few days ago.You can add multiple aggregate functions in PIVOT. Check below query:

select * from (
select * from pivot_string
) PIVOT 
(
  SUM(COL3_ID) as id,MIN(COL3_DESC) as descp 
  for col3 in ('RO' as RO, 'IT' as IT, 'US' as US, 'WE' as WE)
 );

Reference: Using pivot on multiple columns of an Oracle row

SQL FIDDLE

answered Dec 22, 2017 at 14:49

pOrinG

9454 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aljassi Over a year ago

thank you for the MIN(COL3_DESC). it seem to be the right solution -- Im testing my data

Collectives™ on Stack Overflow

Transpose String colomn values to Row Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related