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;
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.