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;