using sql pivot

Question

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.

Lamak · Accepted Answer · 2013-02-08 16:58:41Z

5

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) );

answered Feb 8, 2013 at 16:58

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taryn · Accepted Answer · 2013-02-08 17:01:50Z

3

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

answered Feb 8, 2013 at 17:01

Taryn

249k57 gold badges374 silver badges409 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-02-08 17:02:27Z

2

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))

answered Feb 8, 2013 at 17:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

using sql pivot

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related