Oracle SQL : Pivot a column

Question

I have table structure like below :

product          supplier            price            qty
-------          --------            -----            ---
SOAP             ABC                 50               10
SOAP             DCE                 50               10
BRUSH            FGH                 30               5

I would like to transform this table into :

product          supplier_1        supplier_2         price            qty
-------          --------          ----------         -----            ---
SOAP             ABC                 DCE              50               10
BRUSH            FGH                                  30               5

how can I do it in SQL? thanks in advance.

What would you want shown if the 2nd supplier had a different price for soap? — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 19, 2014 at 8:31

DirkNM · Accepted Answer · 2014-11-19 11:04:32Z

3

Maybe this helps:

select product,
       max(case when rn = 1 then supplier end) as supplier_1,
       max(case when rn = 2 then supplier end) as supplier_2,
       -- ...
       -- max(case when rn = n then supplier end) as supplier_n,
       avg(price) as price,
       sum(qty) as sum
  from(select t.*,
              row_number() over (partition by product order by supplier) rn
         from your_table t
      )
 group
    by product;

edited Nov 19, 2014 at 11:04

answered Nov 19, 2014 at 9:09

DirkNM

2,66417 silver badges22 bronze badges

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

1 Comment

DirkNM Over a year ago

if you have a maximum number of suppliers per product, you need to add a corresponding number of columns supplier_1 .. supplier_n. There is no dynamic way with static sql (maybe with dynamic sql and plsql).

Frank Schmitt · Accepted Answer · 2014-11-19 08:23:00Z

0

select product,supplier_1,supplier_2,price,qty from table 
model
return all rows
dimension by(product)
measures(supplier,
  lpad(' ',10) supplier_1,
  lpad(' ',10) supplier_2
)
rules upsert(
  supplier_1[0] = supplier['SOAP'],
  supplier_2[0] = supplier['BRUSH']
)

10G or 10G above available.

edited Nov 19, 2014 at 8:23

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered Nov 19, 2014 at 8:00

VikiYang

2331 silver badge10 bronze badges

3 Comments

VikiYang Over a year ago

pivot clause supported in 11G， please note.

Ianthe Over a year ago

So sad, we are still in 10G :(

VikiYang Over a year ago

You can use it fix your problem, I said you could choose another clause "pivot" if you are in 11G

Collectives™ on Stack Overflow

Oracle SQL : Pivot a column

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related