CODEWITHSUNDEEP
sqloracle-databaseunpivot
António Godinho
António Godinho

Reputation: 71

SQL - multiple columns into rows

I'm using Oracle and I want to turn a query that gives me several columns into several rows.

This my my query:

SELECT I.cd_curso, 
       I.cd_discip, 
       ND.ds_discip, 
       I.cd_turma_t, 
       I.cd_turma_p, 
       I.cd_turma_l, 
       I.cd_turma_tp, 
       I.cd_turma_e, 
       I.cd_turma_o, 
       I.cd_turma_c, 
       I.cd_turma_s, 
       Count(*) AS alunos 
FROM   cse.v_vwinscri I, 
       cse.t_tbdiscip ND 
WHERE  I.cd_lectivo = 201314 
       AND I.cd_discip = 911901 
       AND I.cd_discip = ND.cd_discip 
       AND I.cd_curso = 9885 
       AND ( I.cd_turma_t IS NOT NULL 
              OR I.cd_turma_p IS NOT NULL 
              OR I.cd_turma_l IS NOT NULL 
              OR I.cd_turma_tp IS NOT NULL 
              OR I.cd_turma_e IS NOT NULL 
              OR I.cd_turma_o IS NOT NULL 
              OR I.cd_turma_c IS NOT NULL 
              OR I.cd_turma_s IS NOT NULL ) 
GROUP  BY I.cd_curso, 
          I.cd_discip, 
          ND.ds_discip, 
          I.cd_turma_t, 
          I.cd_turma_p, 
          I.cd_turma_l, 
          I.cd_turma_tp, 
          I.cd_turma_e, 
          I.cd_turma_o, 
          I.cd_turma_c, 
          I.cd_turma_s 
ORDER  BY I.cd_curso, 
          I.cd_turma_t, 
          I.cd_turma_p, 
          I.cd_turma_l, 
          I.cd_turma_tp, 
          I.cd_turma_e, 
          I.cd_turma_o, 
          I.cd_turma_c, 
          I.cd_turma_s

And I wish to turn the results CD_TURMA_T, CD_TURMA_P, and so on, into diferent rows as CD_TURMA. Well basically using the first fiels as keys and the others as values, changing them from columns to rows...

Is it possible?

Upvotes: 1

Views: 128

Answers (1)

Rachcha
Rachcha

Reputation: 8816

You can simply use UNION ALL like this:

WITH cte AS (
    SELECT I.cd_curso, 
           I.cd_discip, 
           ND.ds_discip, 
           I.cd_turma_t, 
           I.cd_turma_p, 
           I.cd_turma_l, 
           I.cd_turma_tp, 
           I.cd_turma_e, 
           I.cd_turma_o, 
           I.cd_turma_c, 
           I.cd_turma_s, 
           Count(*) AS alunos 
    FROM   cse.v_vwinscri I, 
           cse.t_tbdiscip ND 
    WHERE  I.cd_lectivo = 201314 
           AND I.cd_discip = 911901 
           AND I.cd_discip = ND.cd_discip 
           AND I.cd_curso = 9885 
           AND ( I.cd_turma_t IS NOT NULL 
                  OR I.cd_turma_p IS NOT NULL 
                  OR I.cd_turma_l IS NOT NULL 
                  OR I.cd_turma_tp IS NOT NULL 
                  OR I.cd_turma_e IS NOT NULL 
                  OR I.cd_turma_o IS NOT NULL 
                  OR I.cd_turma_c IS NOT NULL 
                  OR I.cd_turma_s IS NOT NULL ) 
    GROUP  BY I.cd_curso, 
              I.cd_discip, 
              ND.ds_discip, 
              I.cd_turma_t, 
              I.cd_turma_p, 
              I.cd_turma_l, 
              I.cd_turma_tp, 
              I.cd_turma_e, 
              I.cd_turma_o, 
              I.cd_turma_c, 
              I.cd_turma_s)
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_t AS cd_trauma,
       alunos
FROM   cte
UNION ALL 
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_p,
       alunos
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_l,
       alunos 
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ND.ds_discip, 
       cd_turma_tp,
       alunos 
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_e, 
       alunos 
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_o, 
       alunos 
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_c, 
       alunos 
FROM   cte
UNION ALL
SELECT cd_curso, 
       cd_discip, 
       ds_discip, 
       cd_turma_s,
       alunos 
FROM   cte;

Upvotes: 1

Related Questions