将列的不同值转换为行postgres [英] turn the distinct value of columns into a rows postgres
本文介绍了将列的不同值转换为行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:从列到行.但这与我的问题相反.
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屋!
查看全文