SQL ORACLE converting columns into rows

Question

I have the result with the columns Indice,valeur1 etc:

Indice value1 value2 value3 value4

1 x maths physics chemistry

2 21/01/18 133 184 115

I would like to display the result in a vertical manner like

Indice,1,2
valeur1, x,21/01/2018
valeur2,maths, 133
valeur3,physics,184
valeur4,chemistry ,115
...
...
...
...

My query:

    select
    'x' as Valeur1,
    'maths' as Valeur2,
    'physics' as Valeur3, 
    'Chemistry' as Valeur4, 
    null as Valeur5, 
    null as Valeur6, 
    null as Valeur7, 
    null as Valeur8, 
    null as Valeur9, 
    null as Valeur10 
    from dual
    union
    select
        2 as indice,
      to_char((x) ) as Valeur1,
      to_char(sum(maths)) as valeur2,
      to_char(sum(physics)) as valeur3,
      to_char(sum(chemisty)) as valeur4,
      null as Valeur5, 
      null as Valeur6, 
      null as Valeur7, 
      null as Valeur8, 
      null as Valeur9, 
      null as Valeur10 

 from t1

I would like to transpose the columns into rows in the result. Could you please suggest me the possible solutions.

William Robertson · Accepted Answer · 2018-11-25 11:24:05Z

You need UNPIVOT.

select * from unpivotdemo;

    INDICE VALEUR1              VALEUR2              VALEUR3              VALEUR4              VALEUR5              VALEUR6              VALEUR7              VALEUR8              VALEUR9              VALEUR10
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
         1 Semaine courante     Semaine precedente   Semaine -2                                                                                                                                         
         2 03/10/18             184                  115                                                                                                                                                

select indice, valeur, description
from   unpivotdemo
unpivot
     ( description for valeur in
       ( valeur1
       , valeur2
       , valeur3
       , valeur4
       , valeur5
       , valeur6
       , valeur7
       , valeur8
       , valeur9
       , valeur10 ) );

    INDICE VALEUR   DESCRIPTION
---------- -------- --------------------
         1 VALEUR1  Semaine courante
         1 VALEUR2  Semaine precedente
         1 VALEUR3  Semaine -2
         2 VALEUR1  03/10/18
         2 VALEUR2  184
         2 VALEUR3  115

6 rows selected

By default null values are excluded. You can include them by adding include nulls after the unpivot keyword.

To include INDICE in the VALEUR column, you can move it from the select list to the unpivot ... in list - however you also need to convert the datatype to match:

select valeur, description
from   ( select to_char(indice) as indice
               , valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8, valeur9, valeur10
         from   unpivotdemo d )
unpivot include nulls
     ( description for valeur in
       ( indice
       , valeur1
       , valeur2
       , valeur3
       , valeur4
       , valeur5
       , valeur6
       , valeur7
       , valeur8
       , valeur9
       , valeur10 ) );

VALEUR   DESCRIPTION
-------- ----------------------------------------
INDICE   1
VALEUR1  Semaine courante
VALEUR2  Semaine precedente
VALEUR3  Semaine -2
VALEUR4  
VALEUR5  
VALEUR6  
VALEUR7  
VALEUR8  
VALEUR9  
VALEUR10 
INDICE   2
VALEUR1  03/10/18
VALEUR2  184
VALEUR3  115
VALEUR4  
VALEUR5  
VALEUR6  
VALEUR7  
VALEUR8  
VALEUR9  
VALEUR10 

22 rows selected

Edit following expanded question:

To aggregate the values into comma-separated strings, you can use LISTAGG:

select valeur, description
from   ( select listagg(indice, ',')   within group (order by indice) as indice
              , listagg(valeur1, ',')  within group (order by indice) as valeur1
              , listagg(valeur2, ',')  within group (order by indice) as valeur2
              , listagg(valeur3, ',')  within group (order by indice) as valeur3
              , listagg(valeur4, ',')  within group (order by indice) as valeur4
              , listagg(valeur5, ',')  within group (order by indice) as valeur5
              , listagg(valeur6, ',')  within group (order by indice) as valeur6
              , listagg(valeur7, ',')  within group (order by indice) as valeur7
              , listagg(valeur8, ',')  within group (order by indice) as valeur8
              , listagg(valeur9, ',')  within group (order by indice) as valeur9
              , listagg(valeur10, ',') within group (order by indice) as valeur10
         from   unpivotdemo )
unpivot include nulls
     ( description for valeur in
       ( indice
       , valeur1
       , valeur2
       , valeur3
       , valeur4
       , valeur5
       , valeur6
       , valeur7
       , valeur8
       , valeur9
       , valeur10 ) );

VALEUR   DESCRIPTION
-------- --------------------------------------------------------------------------------
INDICE   1,2
VALEUR1  Semaine courante,03/10/18
VALEUR2  Semaine precedente,184
VALEUR3  Semaine -2,115
VALEUR4  
VALEUR5  
VALEUR6  
VALEUR7  
VALEUR8  
VALEUR9  
VALEUR10 

11 rows selected

Note that the comma-separated strings could get long, and by default you are limited to 4000 characters which might be about 200 rows based on your sample data.

Thanks William Roberston for your help.I am trying to adapt your query with mine.But I couldn't succeed yet .

William Robertson · Accepted Answer · 2018-11-25 10:46:58Z

0

You need to use something like

select * from (
    select indice from table union all
    select value1 from table union all
    select value2....union all
    select last from table
)

Or else make use of unpivot as

select column from table
unpivot ( column for column in (indice, value1, ... valuen) )

edited Nov 25, 2018 at 10:46

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

answered Nov 25, 2018 at 9:48

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

SQL ORACLE converting columns into rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related