Skip to Main Content
  • Questions
  • Problem with the pivot and unpivot functions - is it possible to merge numbers with text?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, emanuele.

Asked: December 02, 2021 - 10:35 am UTC

Last updated: December 03, 2021 - 6:06 pm UTC

Version: 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.7.0.0.0

Viewed 1000+ times

You Asked

Hello everyone, I ask for help to solve a problem with the pivot and unpivot functions. is it possible to merge numbers with text? in the result I would like to add the row of the column ects_cod. Thank you

CDS_COD;VALORE;31;30;29;28;27;26;25;24;23;22;21;20;19;18
AG0060;vote;31;30;29;28;27;26;25;24;23;22;21;20;19;18
AG0060;tot;297;655;509;731;632;583;496;427;316;282;226;214;142;169
AG0060;%;5;12;9;11;11;10;9;8;6;5;4;4;3;3
AG0061;ects_cod;A;A-B;B;B-C;C;C;C-D;D;D;D;D;E;E;E


with LiveSQL Test Case:

and Chris said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Emanuele, December 03, 2021 - 9:39 am UTC

I had found another solution, but yours is much simpler and more functional. I am very grateful to you. best regards
Chris Saxon
December 03, 2021 - 6:05 pm UTC

You're welcome

Just adding some polish

Stew Ashton, December 03, 2021 - 10:53 am UTC

This is just a slight variant of the answer by Chris. It gets exactly the requested result. Notice you have to pre-sum the NUM_AD and PERC_AD numbers.

Always unpivot before pivoting: it is so much easier to code and read.

with rws as (
  select cds_cod
    , voto voto_header
    , to_char ( voto ) voto
    , to_char ( sum(num_ad) ) num_ad
    , to_char ( sum(perc_ad) ) perc_ad
    , ects_cod
  from  ects_ds 
  group by cds_cod, ects_cod, voto
)
select * from rws
unpivot  ( 
  val for valore in (
    voto as 'vote', num_ad as 'tot', perc_ad as '%', ects_cod as 'ects_cod'
  )  
) 
pivot (
  max ( val ) for voto_header in (
    31,30,29,28,27,26,25,24,23,22,21,20,19,18
  )
)
order by case valore when 'vote' then 1 when 'tot' then 2 when '%' then 3 else 4 end;

CDS_COD   VALORE     31    30    29    28    27    26    25    24    23    22    21    20    19    18   
-------   --------   --    --    --    --    --    --    --    --    --    --    --    --    --    --
AG0060    vote       31    30    29    28    27    26    25    24    23    22    21    20    19    18     
AG0060    tot        297   655   509   731   632   583   496   427   316   282   226   214   142   169    
AG0060    %          5     12    9     11    11    10    9     8     6     5     4     4     3     3      
AG0060    ects_cod   A     A-B   B     B-C   C     C     C-D   D     D     D     D     E     E     E      
Best regards, Stew Ashton
Chris Saxon
December 03, 2021 - 6:06 pm UTC

Great stuff Stew, thanks for sharing

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.