CODEWITHSUNDEEP
sqloracle11gpivot
cableload
cableload

Reputation: 4375

using sql pivot

I am using oracle 11gR2 running on windows server 2008 R2

I have a table like this

   year    code    value
   ----    ----    -----
   1991      1      v1
   1991      2      v2
   1991      3      v3

   1992      1     v4
   1992      2     v5
   1992      3     v6

   ...
   2050

The code column can be from 1 through 10.

I would like to see the final output as (columns having value 1 through 10)

 year    1   2   3  4 ....    10
 ----    --  --  -- --        --
 1991   v1   v2  v3 null.......null
 1992   v4   v5  v6. ...etc

How can i do the pivoting to get the result in the above format? Thanks in advance.

Upvotes: 1

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

In 11g, you can use pivot:

SELECT *
FROM t
PIVOT (max(value) as value FOR (code) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Upvotes: 2

Taryn
Taryn

Reputation: 247720

Oracle 11g has a PIVOT function that can be used to transform the data into columns from rows:

select *
from
(
  select year, code, value
  from yourtable
)
pivot
(
  max(value)
  for code in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10')
) piv

See SQL Fiddle with Demo

Upvotes: 3

Lamak
Lamak

Reputation: 70648

One easy way without using PIVOT is using CASE:

SELECT  year,
        MIN(CASE WHEN code = 1 THEN value END) AS Col1,
        MIN(CASE WHEN code = 2 THEN value END) AS Col2,
        MIN(CASE WHEN code = 3 THEN value END) AS Col3,
        MIN(CASE WHEN code = 4 THEN value END) AS Col4,
        MIN(CASE WHEN code = 5 THEN value END) AS Col5,
        MIN(CASE WHEN code = 6 THEN value END) AS Col6,
        MIN(CASE WHEN code = 7 THEN value END) AS Col7,
        MIN(CASE WHEN code = 8 THEN value END) AS Col8,
        MIN(CASE WHEN code = 9 THEN value END) AS Col9,
        MIN(CASE WHEN code = 10 THEN value END) AS Col10
FROM YourTable
GROUP BY year

With PIVOT, it would be like this:

SELECT *
FROM (SELECT year, code, value FROM YourTable) 
PIVOT (MIN(value) FOR code IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) );

Upvotes: 5

Related Questions