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, Apr 16 '18 at 09:15
@jarlh i am using Postgresql , the purpose for is that it can be a model for both — dev_medo, Apr 16 '18 at 09:21
What is the data type of `valueofname` ? Please add table schema. — DEarTh, Apr 16 '18 at 09:23
Add some sample table data and the expected result. (As formatted text, not images.) — jarlh, Apr 16 '18 at 09:30
Remove the `and ad_id='xxx' ` parts from the case expressions. (Handled by GROUP BY.) — jarlh, Apr 16 '18 at 09:31
You can use [`NULLIF`](https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-NULLIF) if you want `NULL` instead of `""`. — mkopriva, Apr 16 '18 at 09:33
Thanks @TimBiegeleisen for the collaboration what you know is just bad voting instead of helping — dev_medo, Apr 16 '18 at 09:39
@mkopriva [The start of my problem ](https://stackoverflow.com/questions/49793575/turn-the-distinct-value-of-columns-into-a-rows-postgres/49853146#49853146). so now when i got the ad_id as condition if existed it returns the valueofname else return NULL for each name — dev_medo, Apr 16 '18 at 09:39
mr @jarlh here is more details on my problem :https://stackoverflow.com/questions/49793575/turn-the-distinct-value-of-columns-into-a-rows-postgres/49853146#49853146. thanks indeed — dev_medo, Apr 16 '18 at 09:41
@a_horse_with_no_name hope that is more clear , thanks indeed for your help — dev_medo, Apr 16 '18 at 09:59
@jarlh i edited hope that is more clear , sorry for any unclear things i still learning how to use the platform — dev_medo, Apr 16 '18 at 10:15
@dev_medo Something like this? http://www.sqlfiddle.com/#!17/8603ee/3 (updated link) — mkopriva, Apr 16 '18 at 10:33
@mkopriva yeep that's totally what i am looking for as a result i used your query but i am getting just a blank instead of NULL , i looked at the description of my table and i found that value is set as not NULL does make any sence ?? for not accepting to be NULL — dev_medo, Apr 16 '18 at 11:00
@dev_medo how did you test the query? For example `psql` by defaut displays NULLs as blanks and so cannot be easily discerned from an empty string. If you do `SELECT NULL;` do you see NULL? or a blank? or something else? While in `psql` you can change how NULLs are displayed with this command `\pset null '(null)'`, this will display NULLs as the string *(null)*. — mkopriva, Apr 16 '18 at 11:31
yeep i checked that and it was the case when i parse them into json thanks indeed for @mkopriva — dev_medo, Apr 16 '18 at 13:48
@ mkopriva could you please review this https://stackoverflow.com/questions/49859521/merge-two-queries-with-join-and-where-and-group-by-conditions-golang-postg/49860569#49860569 — dev_medo, Apr 16 '18 at 15:46

score 0 · Answer 1 · answered Apr 16 '18 at 10:55

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.

@ 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 NULL`is not applicated — dev_medo, Apr 16 '18 at 11:11

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

1 Answers1