To unpivot, all columns must be compatible types. If you have a mix of characters and numbers, you'll have to to_char all the non-string values.
I'm a little unsure exactly what you're trying to do - hopefully this example will help.
The subquery converts everything to strings first. It also assigns consecutive row numbers for each code.
This allows you to unpivot the columns you want, then pivot back based on the calculated row number. I find this easier to write and follow than pivoting, then unpivoting.
It also has the benefit of being "dynamic" - it works with any values for the pivot column (voto) as long as you know the maximum number of values for each code:
CREATE TABLE ects_ds (
cds_cod VARCHAR2(10 CHAR)
, ects_cod VARCHAR2(5)
, num_ad NUMBER(5)
, perc_ad NUMBER(5, 2)
, voto NUMBER(2)
);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto) VALUES ( 'AG0060' , 'A' , 297 , 5 , 31);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'A-B' ,655, 12, 30);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'B' ,509 ,9 ,29);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'B-C', 731 ,11 ,28);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'C' ,632, 11 ,27);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'C' ,583 ,10, 26);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'C-D', 496, 9 ,25);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'D' ,427 ,8 ,24);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'D' ,316 ,6 ,23);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'D' ,282, 5 ,22);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'D' ,226, 4 ,21);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'E' ,214 ,4 ,20);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'E' ,142 ,3 ,19);
INSERT INTO ects_ds ( cds_cod , ects_cod , num_ad , perc_ad , voto ) VALUES ('AG0060', 'E' , 169 , 3, 18 );
with rws as (
select
cds_cod
, to_char ( voto ) voto
, to_char ( perc_ad ) perc_ad
, to_char ( num_ad ) num_ad
, ects_cod
, row_number () over (
partition by cds_cod
order by voto desc
) rn
from ects_ds
)
select * from rws
unpivot (
val for col in (
ects_cod, num_ad, perc_ad, voto
)
)
pivot (
max ( val ) for rn in (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14
)
);
CDS_COD COL 1 2 3 4 5 6 7 8 9 10 11 12 13 14
AG0060 ECTS_COD A A-B B B-C C C C-D D D D D E E E
AG0060 NUM_AD 297 655 509 731 632 583 496 427 316 282 226 214 142 169
AG0060 PERC_AD 5 12 9 11 11 10 9 8 6 5 4 4 3 3
AG0060 VOTO 31 30 29 28 27 26 25 24 23 22 21 20 19 18
The downside is the column names are now generic numbers from 1 instead of the actual row values.