CODEWITHSUNDEEP
oracle-databaseoracle11g
mpruchni
mpruchni

Reputation: 300

Insert values from different rows into one row and multiple columns grouping by specific value of column x

Sorry if Im not clear in title, I have some language problems. I have a table (quite big) like 

   Person    value    Letter
------------------------------------------
    Tom      value1     A      
    Tom      value2     T         
    Ann      value1     F    
    Ann      value2     R
    Ann      value3     Y 
    Jim      value3     W

I would like to shorten it into:

   Person    value1    value2    value3
------------------------------------------
    Tom        A        T          (null)
    Ann        F        R            Y   
    Jim     (null)    (null)         W

Something like listagg, but into different columns. Looks simple but I'm stuck...

edit: There are 8 values, not just 3, I wanted made it simplier, values don't repeat for the same person (but can be null/not appear))

Upvotes: 0

Views: 24

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

Here's one option: the idea is to use an aggregate function (such as MIN, MAX, SUM). As your sample data contain only 3 values, that's what I did as well - you'd use 5 more lines like this (including them from line #13 onward).

Note that lines #1 - 8 represent your sample data; you already have them stored in the table so you wouldn't be typing that. Code you actually need begins at line #9.

SQL> with test (person, value, letter) as
  2    (select 'Tom', 'val1', 'A' from dual union all
  3     select 'Tom', 'val2', 'T' from dual union all
  4     select 'Ann', 'val1', 'F' from dual union all
  5     select 'Ann', 'val2', 'R' from dual union all
  6     select 'Ann', 'val3', 'Y' from dual union all
  7     select 'Jim', 'val3', 'W' from dual
  8    )
  9  select
 10    person,
 11    max(decode(value, 'val1', letter)) value1,
 12    max(decode(value, 'val2', letter)) value2,
 13    max(decode(value, 'val3', letter)) value3
 14  from test
 15  group by person
 16  order by person;

PERSON VALUE1 VALUE2 VALUE3
------ ------ ------ ------
Ann    F      R      Y
Jim                  W
Tom    A      T

SQL>

Upvotes: 1

Related Questions