如果行上的多个条件不存在，则返回 NULL postgreSQL

Question

我有一个这样的模式：

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

行名包含多个值：年龄、姓名、生日、工作、年龄 我想将其转换为：

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

我在下面做了这个查询选择来修复它，所以在我的程序中，我必须得到结果，其中 ad_id ='xxxx' 对于每个 name =' name 或 age 或 birthday 或 job '

有些ad_id没有所有的名称，正如您可能在架构下方看到的那样，ad_id= 1只有name和age，但没有job，所以我希望当找不到作业时，它会返回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;

Answer 1

要平展所有ad_id的记录，请使用：

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;

要获取单个ad_id的结果，请添加 where 子句：

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;

注意：如果没有带有ad_id = 'xxx'的行，则不会返回任何行。