将列的不同值转换为行 postgres [英] turn the distinct value of columns into a rows postgres

问题描述

我有一个类似的架构:

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

行名包含多个值:age、name、birthday、job、age我想把它转换成这个:

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]
         [valueofname] [valueofname] [valueofname] [valueofname]

我已经完成了每一行的查询:

I have done the query for each line:

select * from where name='name'
select * from where name='age'
select * from where name='job'

我看到了示例 SQL Server : Columns to Rows.但这与我的问题相反.

I saw the example SQL Server : Columns to Rows. But it's the opposite of my problem.

您对在性能方面进行可扩展查询有什么建议吗?

Do you have any suggestion for making one scalable query in term of performance?

推荐答案

可以使用条件聚合:

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;

在 SQL Server 中，您也可以使用 pivot 执行类似的操作.

In SQL Server, you can also do something similar with pivot.

这篇关于将列的不同值转换为行 postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！