0

I have data like the following in an Oracle database

select * from TABLE_1

ID       Field      Value
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

I need the result in below format

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married

I wrote a query

select  
   ID,  

    coalesce(sum(case when Field = 'name' then value end), 0) as "Name", 
    coalesce(sum(case when Field = 'age' then value end), 0) as "Age", 
    coalesce(sum(case when Field = 'gender' then value end), 0) as "Gender"
 from "TABLE_1"  
group by ID;

I am getting a conversion error:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER.

What am I doing wrong?

1

Column value is a string type (CHAR, VARCHAR2). You can not SUM strings. Use another aggregate function, e.g. MIN. And you do not need COALESCE.

select  
   ID,  
    min(case when Field = 'name' then value end) as "Name", 
    min(case when Field = 'age' then value end) as "Age", 
    min(case when Field = 'gender' then value end) as "Gender",
    min(case when Field = 'status' then value end) as "Status"
 from "TABLE_1"  
group by ID;

        ID Name  Age   Gender Status
---------- ----- ----- ------ --------
         1 Bob   30    male
         2 Susan              married
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.