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)
)Table created.
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 );SELECT
*
FROM
(
SELECT
cds_cod
, voto
, perc_ad
, num_ad
-- , ects_cod
FROM
ects_ds
) PIVOT (
SUM ( perc_ad )
perc_ad, SUM ( num_ad ) num_ad, SUM ( voto ) voto
FOR voto
IN ( 18 "18", 19 "19", 20 "20", 21 "21", 22 "22", 23 "23", 24 "24", 25 "25", 26 "26", 27 "27", 28 "28", 29 "29", 30 "30", 31 "31" )
)
unpivot
( ( "31" , "30" , "29" , "28" , "27" , "26" , "25" , "24" , "23" , "22" , "21" , "20" , "19" , "18" ) FOR VALORE IN ( ( "31_VOTO" , "30_VOTO" ,
"29_VOTO" , "28_VOTO" , "27_VOTO" , "26_VOTO" , "25_VOTO" , "24_VOTO" , "23_VOTO" , "22_VOTO" , "21_VOTO" , "20_VOTO" , "19_VOTO" , "18_VOTO" )
AS 'vote' , ( "31_NUM_AD" , "30_NUM_AD" , "29_NUM_AD" , "28_NUM_AD" , "27_NUM_AD" , "26_NUM_AD" , "25_NUM_AD" , "24_NUM_AD" , "23_NUM_AD" ,
"22_NUM_AD" , "21_NUM_AD" , "20_NUM_AD" , "19_NUM_AD" , "18_NUM_AD" ) AS 'tot' , ( "31_PERC_AD" , "30_PERC_AD" , "29_PERC_AD" ,
"28_PERC_AD" , "27_PERC_AD" , "26_PERC_AD" , "25_PERC_AD" , "24_PERC_AD" , "23_PERC_AD" , "22_PERC_AD" , "21_PERC_AD" , "20_PERC_AD" ,
"19_PERC_AD" , "18_PERC_AD" ) AS '%' ) );