CODEWITHSUNDEEP
sqloracle11gtranspose
Varun
Varun

Reputation: 5061

Transpose a query output

I have a normal select query which results following output.

select cid,x1,x2,x3,x4,fy
  from temp_table;

cid     x1  x2  x3  x4  fy
----------------------------
6657    100 0   0   200 2014
6658    300 0   0   400 2015
6659    500 0   0   600 2016

I want it to rewrite it print following output.

    2014    2015    2016    
-------------------------   
x1  100     300     500     
x2  0       0       0       
x3  0       0       0       
x4  200     400     600

How can this be achieved?

Upvotes: 1

Views: 8884

Answers (4)

DB_learner
DB_learner

Reputation: 1026

You can do it with combination of unpivot and pivot.

select * from 
    (select * from 
    (select x1,x2,x3,x4,fy from table1) 
    unpivot(val for x in (x1,x2,x3,x4)))
pivot(sum(val) for fy in (2014,2015,2016))

Upvotes: 0

Srini V
Srini V

Reputation: 11355

Three possible options to transpose in Oracle 11G

  • DECODE Option

    • a. Use SUM and DECODE for the new columns if you want to transpose and summarize

    • b. Use MAX and DECODE for the new columns if you want to transpose and not to summarize

  • Use PIVOT

  • Use WITH and SUB SELECT as said by Gordon

To use pivot, it is not possible in your code since your initial data set is already pivoted.

WITH TEMP1
    AS (SELECT
             6657 AS CID,
             100 AS VAL,
             'X1' AS XCORD,
             2014 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6657 AS CID,
             0 AS VAL,
             'X2' AS XCORD,
             2014 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6657 AS CID,
             0 AS VAL,
             'X3' AS XCORD,
             2014 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6657 AS CID,
             200 AS VAL,
             'X4' AS XCORD,
             2014 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6658 AS CID,
             300 AS VAL,
             'X1' AS XCORD,
             2015 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6658 AS CID,
             0 AS VAL,
             'X2' AS XCORD,
             2015 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6658 AS CID,
             0 AS VAL,
             'X3' AS XCORD,
             2015 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6658 AS CID,
             400 AS VAL,
             'X4' AS XCORD,
             2015 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6659 AS CID,
             500 AS VAL,
             'X1' AS XCORD,
             2016 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6659 AS CID,
             0 AS VAL,
             'X2' AS XCORD,
             2016 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6659 AS CID,
             0 AS VAL,
             'X3' AS XCORD,
             2016 AS FY
        FROM
             DUAL
        UNION ALL
        SELECT
             6659 AS CID,
             600 AS VAL,
             'X4' AS XCORD,
             2016 AS FY
        FROM
             DUAL)
SELECT
      *
FROM
      (SELECT
            XCORD,
            FY,
            SUM ( VAL ) AS VAL
       FROM
            TEMP1
       GROUP BY
            XCORD,
            FY) PIVOT (SUM ( VAL ) FOR FY IN ('2014', '2015', '2016'))
ORDER BY
      XCORD;

Results:

XCORD   '2014'  '2015'  '2016'
-----   ------  ------  ------
X1  100 300 500
X2  0   0   0
X3  0   0   0
X4  200 400 600

If you look into the data set you can see that the results are

CID     VAL     XCORD   FY
----    ----    -----   ----
6657    100 X1  2014
6657    0   X2  2014
6657    0   X3  2014
6657    200 X4  2014
6658    300 X1  2015
6658    0   X2  2015
6658    0   X3  2015
6658    400 X4  2015
6659    500 X1  2016
6659    0   X2  2016
6659    0   X3  2016
6659    600 X4  2016

Which is more or less a UNPIVOT of your data.

Upvotes: 0

Sergej Panic
Sergej Panic

Reputation: 839

More general solution is with UNPIVOT and PIVOT.
Your temp_table is already a pivot table with (x1 x2 x3 x4) as x-axis and fy as y-axis.
First we need to UNPIVOT temp_table into unpivoted_temp_table and then PIVOT it with fy as x-axis and (x1 x2 x3 x4) as y-axis:

with unpivoted_temp_table as (

SELECT *
  FROM   temp_table
  UNPIVOT (
          totalSales                            
          FOR x                             
          IN  (x1, x2, x3, x4)
         )
)  

select *  
FROM   unpivoted_temp_table 
  PIVOT (
               SUM(totalSales)        
           FOR fy          
          IN (2014, 2015, 2016)
         )
order by 1 --order by column X

http://sqlfiddle.com/#!4/4fdfc/1/0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Here is a way to do this with just subqueries and aggregation:

select name,
       sum(case when fy = 2014 then x end) as "2014",
       sum(case when fy = 2015 then x end) as "2015",
       sum(case when fy = 2016 then x end) as "2016"
from (select fy,
             (case when n.n = 1 then 'x1'
                   when n.n = 2 then 'x2'
                   when n.n = 3 then 'x3'
                   when n.n = 4 then 'x4'
              end) as name,
             (case when n.n = 1 then x1
                   when n.n = 2 then x2
                   when n.n = 3 then x3
                   when n.n = 4 then x4
              end) as x
      from temp_table cross join
            (select 1 as n from dual union all
             select 2 from dual union all
             select 3 from dual union all
             select 4 from dual
            ) n
     ) t
group by name;

You can also use pivot, but that is a very recent addition to Oracle SQL, so I'm inclined to use this method.

Upvotes: 1

Related Questions