Btukfyl

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.

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.

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) )