Oracle sql pivot columns by type

Question

few days before I asked similar question.

I have got a table containing material types:

id  type    mat_number   description      count  
------------------------------------------------    
a   mat_1   123456       wood type a        5  
a   mat_2   333333       plastic type a     8  
b   mat_1   654321       wood type b        7  
c   mat_2   444444       plastic type c    11  
d   mat_1   121212       wood type z        8  
d   mat_2   444444       plastic type c     2  
d   mat_2   555555       plastic type d     3

with SQL I want to create list as follows:

id  mat_1     description     count   mat_2     description    count  
-------------------------------------------------------------------  
a   123456    wood type a      5      333333    plastic type c   8  
b   654321    wood type b      7      null  
c   null                              444444    plastic type c   11   
d   121212    plastic type c   8      444444    plastic type c   2  
d   null                              555555    plastic type c   3

Is that possible with pivot?

While not a complete answer, http://stackoverflow.com/questions/32828265/rows-to-column-in-oracle#32828478 is a similar question with code that may be able to help — Taku_, Oct 19 '15 at 12:39

score 1 · Answer 1 · answered Oct 19 '15 at 12:43

no but you can do it with a self-join

select a.id aId, a.mat_number mat1, a.description aDescrip, a.count aCount,
                 b.mat_number mat2, b.description aDescrip, b.count bCount
From table a
   full join table b
       on b.id = a.id 
          and a.type = mat_1
          and b.type = mat_2

score 1 · Answer 2 · answered Oct 19 '15 at 12:52

One does not need to use the word "pivot" in a query to achieve the wanted transformation. A few simple case expressions and a group by can do wonders.

select
      id
    , max(case when type = 'mat_1' then MAT_NUMBER end) as mat_1     
    , max(case when type = 'mat_1' then DESCRIPTION end) as description_1
    , max(case when type = 'mat_1' then COUNT end) as count_1
    , max(case when type = 'mat_2' then MAT_NUMBER end) as mat_2
    , max(case when type = 'mat_2' then DESCRIPTION end) as description_2
    , max(case when type = 'mat_2' then COUNT end) as count_2
from table1
group by
      id
order by
      id
;

Oracle sql pivot columns by type

2 Answers2