how to use Pivot SQL

Question

For Example,

select A,B,C,D,E,YEAR
FROM t1
where t1.year = 2018
UNION ALL
select A,B,C,D,E,YEAR
FROM t2
where t2.year = 2017

execute like this

A --- B----C----D----E----YEAR
2 --- 4----6----8----10---2018
1 --- 3----5----7----9----2017

I would like to have a result like this

  2018  2017
A  2    1
B  4    3
C  6    5
D  8    7
E  10   9

I know I should use pivot, and googled around, but I can not figure out how to write a code to have a result like above.

Thanks

Answer 1

Assuming you are using Oracle 11.1 or above, you can use the pivot and unpivot operators. In your problem, the data is already "pivoted" one way, but you want it pivoted the other way; so you must un-pivot first, and then re-pivot the way you want it. In the solution below, the data is read from the table (I use a WITH clause to generate the test data, but you don't need the WITH clause, you can start at SELECT and use your actual table and column names). The data is fed through unpivot and then immediately to pivot - you don't need subqueries or anything like that.

Note about column names: don't use year, it is an Oracle keyword and you will cause confusion if not (much) worse. And in the output, you can't have 2018 and such as column names - identifiers must begin with a letter. You can go around these limitations using names in double quotes; that is a very poor practice though, best left just to the Oracle parser and not used by us humans. You will see I called the input column yr and the output columns y2018 and such.

with
  inputs ( a, b, c, d, e, yr ) as (
    select 2, 4, 6, 8, 10, 2018 from dual union all
    select 1, 3, 5, 7,  9, 2017 from dual
  )
select   col, y2018, y2017
from     inputs
unpivot  ( val for col in (a as 'A', b as 'B', c as 'C', d as 'D', e as 'E') )
pivot    ( min(val) for yr in (2018 as y2018, 2017 as y2017) )
order by col   --   if needed
;

COL      Y2018      Y2017
--- ---------- ----------
A            2          1
B            4          3
C            6          5
D            8          7
E           10          9

ADDED:

Here is how this used to be done (before the pivot and unpivot were introduced in Oracle 11.1). Unpivoting was done with a cross join to a small helper table, with a single column and as many rows as there were columns to unpivot in the base table - in this case, five columns, a, b, c, d, e need to be unpivoted, so the helper table has five rows. And pivoting was done with conditional aggregation. Both can be combined into a single query - there is no need for subqueries (other than to create the helper "table" or inline view).

Note, importantly, that the base table is read just once. Other methods of unpivoting are much more inefficient, because they require reading the base table multiple times.

select decode(lvl, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5, 'E') as col,
       max(case yr when 2018 then decode(lvl, 1, a, 2, b, 3, c, 4, d, 5, e) end) as y2018,
       max(case yr when 2017 then decode(lvl, 1, a, 2, b, 3, c, 4, d, 5, e) end) as y2017
from   inputs cross join ( select level as lvl from dual connect by level <= 5 )
group by decode(lvl, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5, 'E')
order by decode(lvl, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5, 'E')
;

This looks worse than it is; the same decode() function is called three times, but with exactly the same arguments, so it is calculated only once, the value is cached and it is reused in the other places. (It is calculated for group by and then reused for select and for order by.)

To test, you can use the same WITH clause as above - or your actual data.

decode() is proprietary to Oracle, but the same can be written with case expressions (essentially identical to the decode() approach, just different syntax) and it will work in most other database products.