CODEWITHSUNDEEP
sqloracle-database
milheiros
milheiros

Reputation: 619

Conditional transpose columns into rows in oracle sql

I have this row in my table mtinre:

  INREPRCO   INRESELO   INRECAPI   INRECFRA   INRECAPO
---------- ---------- ---------- ---------- ----------
     32.42       1.87          1                     5

I build a query to transpose this row as 5 different rows for each column.

SELECT CASE pivot
          WHEN 1 THEN 'VAPRS'
          WHEN 2 THEN 'VAFRC'
          WHEN 3 THEN 'VACTA'
          WHEN 4 THEN 'VIMSL'
          WHEN 5 THEN 'VINEM'
       END
          component,
       CASE pivot
          WHEN 1 THEN inreprco
          WHEN 2 THEN inrecfra
          WHEN 3 THEN inrecapo
          WHEN 4 THEN inreselo
          WHEN 5 THEN inreinem
          ELSE NULL
       END
          VALUE,
       CASE pivot
          WHEN 4
          THEN
             (NVL (inreprco, 0) + NVL (inrecfra, 0) + NVL (inrecapo, 0))
          WHEN 5
          THEN
             (NVL (inreprco, 0) + NVL (inrecfra, 0) + NVL (inrecapo, 0))
          ELSE
             NULL
       END
          AS base
  FROM mtinre,
       (    SELECT ROWNUM pivot
              FROM DUAL
        CONNECT BY LEVEL <= 5)

The output is:

COMPONENT      VALUE       BASE
--------- ---------- ----------
VAPRS          32.42           
VAFRC                          
VACTA              5           
VIMSL           1.87      37.42
VINEM            .94      37.42

But these 5 fields(INREPRCO,INRESELO,INRECAPI,INRECFRA,INRECAPO) can have null or zero(0) values. So I need to select only those how have values. In the last example, just show me:

COMPONENT      VALUE       BASE
--------- ---------- ----------
VAPRS          32.42           
VACTA              5           
VIMSL           1.87      37.42
VINEM            .94      37.42

I've tried to put some where conditions, but the connect by level statement creates me always 5 rows.

So, I changed my query and made this:

SELECT *
  FROM (SELECT CASE pivot
                  WHEN 1 THEN 'VAPRS'
                  WHEN 2 THEN 'VAFRC'
                  WHEN 3 THEN 'VACTA'
                  WHEN 4 THEN 'VIMSL'
                  WHEN 5 THEN 'VINEM'
               END
                  component,
               CASE pivot
                  WHEN 1 THEN inreprco
                  WHEN 2 THEN inrecfra
                  WHEN 3 THEN inrecapo
                  WHEN 4 THEN inreselo
                  WHEN 5 THEN inreinem
                  ELSE NULL
               END
                  VALUE,
               CASE pivot
                  WHEN 4
                  THEN
                     (  NVL (inreprco, 0)
                      + NVL (inrecfra, 0)
                      + NVL (inrecapo, 0))
                  WHEN 5
                  THEN
                     (  NVL (inreprco, 0)
                      + NVL (inrecfra, 0)
                      + NVL (inrecapo, 0))
                  ELSE
                     NULL
               END
                  AS base
          FROM mtinre,
               (    SELECT ROWNUM pivot
                      FROM DUAL
                CONNECT BY LEVEL <= 5))
 WHERE VALUE IS NOT NULL

It works, but is there any other way to do that without using a sub select statement? Any suggestion?

Thanks Filipe

Upvotes: 1

Views: 596

Answers (1)

San
San

Reputation: 4538

Using UNPIVOT and a little trick can do the job. Almost all tables have an id column (primary or unique key). Assuming that the table has id_col as id column, this query will do the job

SQL> WITH table_(id_col, inreprco,inreselo,inrecapi,inrecfra,inrecapo) AS
  2   (SELECT 1, 32.42,1.87,0.94,NULL,5 FROM dual UNION ALL
  3    SELECT 2, 33.43,2.87,0.87,12,9 FROM dual ),
  4  ---------
  5  -- End of data preparation
  6  ---------
  7  table2_ AS (SELECT id_col, component, VALUE
  8               FROM table_
  9            UNPIVOT (VALUE FOR component IN (inreprco AS 'VAPRS', inrecfra AS 'VAFRC', inrecapo AS 'VACTA', inreselo AS 'VIMSL', inrecapi AS 'VINEM')))
 10  select a.id_col,
 11         a.COMPONENT,
 12         a.VALUE,
 13         CASE WHEN a.component IN ('VIMSL', 'VINEM') THEN nvl(b.inreprco, 0) + nvl(b.inrecfra, 0) + NVL(b.inrecapo, 0) ELSE NULL END AS base
 14    FROM table2_ a
 15    INNER JOIN table_ b
 16       ON b.id_col = a.id_col;

    ID_COL COMPONENT      VALUE       BASE
---------- --------- ---------- ----------
         1 VAPRS          32.42 
         1 VACTA              5 
         1 VIMSL           1.87      37.42
         1 VINEM           0.94      37.42
         2 VAPRS          33.43 
         2 VAFRC             12 
         2 VACTA              9 
         2 VIMSL           2.87      54.43
         2 VINEM           0.87      54.43

9 rows selected

But if there are no Id column, then modifying the join as cross join will do but that will return correct result if there is only one row in the table.

   SQL> WITH table_(inreprco,inreselo,inrecapi,inrecfra,inrecapo) AS
      2   (SELECT 32.42,1.87,0.94,NULL,5 FROM dual),
      3  ---------
      4  -- End of data preparation
      5  ---------
      6  table2_ AS (SELECT component, VALUE
      7               FROM table_
      8            UNPIVOT (VALUE FOR component IN (inreprco AS 'VAPRS', inrecfra AS 'VAFRC', inrecapo AS 'VACTA', inreselo AS 'VIMSL', inrecapi AS 'VINEM')))
      9  select a.COMPONENT,
     10         a.VALUE,
     11         CASE WHEN a.component IN ('VIMSL', 'VINEM') THEN nvl(b.inreprco, 0) + nvl(b.inrecfra, 0) + NVL(b.inrecapo, 0) ELSE NULL END AS base
     12    FROM table2_ a
     13    CROSS JOIN table_ b
     14  /

    COMPONENT      VALUE       BASE
    --------- ---------- ----------
    VAPRS          32.42 
    VACTA              5 
    VIMSL           1.87      37.42
    VINEM           0.94      37.42

Or wait for someone who comes with some other approach ;)

Upvotes: 3

Related Questions