multiple condition on a row if not existed return a NULL postgresql

Question

I have a schema like:

[ad_id] .  [name] . [valueofname]
 1 .        name .    "brian"
 1 .        age  .    "23"
 2 .        job  .    "IT"
 2 .        name .    "Jack"

the row name contains multiple values : age , name, birthday, job, age I'd like to convert it into this:

[ad_id] .      [name]  .       [age] .              [birthday] .    [job]
[valueofad_id][valueofname] [valueofnameofage] [valueofnameofbirth] [valueofnameofjob]

I did this query selection below to fix it , so in my program i must get the result where ad_id='xxxx' for each when name='name or age or birthday or job '

Some ad_id have not all the names , as you may see below the schema the ad_id= 1 has just the name and age but not the job so i want when the job not found it returns a NULL

 [ad_id] .  [name] . [valueofname]
  1 .        name .    "brian"
  1 .        age  .    "23"
  2 .        job  .    "IT"
  2 .        name .    "Jack" 


select ad_id,

max(case when name = 'name' and ad_id='xxx' then valueofname end) as name,
max(case when name = 'age' and ad_id='xxx'  then valueofname end) as age,
max(case when name = 'birthday' and ad_id='xxx' then valueofname end) as birthday,
max(case when name = 'job' and ad_id='xxx' then valueofname end) as job

from t
group by ad_id;

Are you using MySQL or Postgresql? (Don't add tags for products not involved.) — jarlh
– jarlh, Commented Apr 16, 2018 at 9:15
@jarlh i am using Postgresql , the purpose for is that it can be a model for both — dev_medo
– dev_medo, Commented Apr 16, 2018 at 9:21
What is the data type of valueofname ? Please add table schema. — DEarTh
– DEarTh, Commented Apr 16, 2018 at 9:23
Add some sample table data and the expected result. (As formatted text, not images.) — jarlh
– jarlh, Commented Apr 16, 2018 at 9:30

Gordon Linoff · Accepted Answer · 2018-04-16 10:55:40Z

0

To flatten the records for all ad_ids, use:

select ad_id,
       max(case when name = 'name' then valueofname end) as name,
       max(case when name = 'age'then valueofname end) as age,
       max(case when name = 'birthday' then valueofname end) as birthday,
       max(case when name = 'job' then valueofname end) as job
from t
group by ad_id;

To get the results for a single ad_id, add a where clause:

select ad_id,
       max(case when name = 'name' then valueofname end) as name,
       max(case when name = 'age'then valueofname end) as age,
       max(case when name = 'birthday' then valueofname end) as birthday,
       max(case when name = 'job' then valueofname end) as job
from t
where ad_id = 'xxx' 
group by ad_id;

Note: If there are no rows with ad_id = 'xxx' then this will return no rows.

answered Apr 16, 2018 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

dev_medo Over a year ago

@ Gordon Linoff It's working moreover the having clause is working also , but the NULL problem still existing for the value field description: [Type:Text ; NOT NULL] but the selection made is just a temporary table so the NOT NULLis not applicated

Collectives™ on Stack Overflow

multiple condition on a row if not existed return a NULL postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related