英文:
How to convert multiple rows into single row but with different column names in postgressql
问题
我想要的输出是：
id name amount1 amount2 amount31 abc 10 5 402 aaa 90 20 10
英文:
My resultant out is this using case when in postgres
id name amount1 amount2 amount31 abc 10 null null1 abc null 5 null1 abc null null 40
I want my output as:
id name amount1 amount2 amount31 abc 10 5 402 aaa 90 20 10
答案1
得分: 0
你可以使用 group by 和聚合函数 max()（如果你想按 id 列对数据进行求和，可以使用 sum()） 来实现：
select id, max(name) as name, max(amount1) as amount1, max(amount2) as amount2, max(amount3) as amount3from mytablegroup by id
英文:
You can do it using group by and the aggregate function max() ( use sum() if you want to sum your data by id ) :
select id, max(name) as name, max(amount1) as amount1, max(amount2) as amount2, max(amount3) as amount3from mytablegroup by id
答案2
得分: 0
你可以使用 group by 和 coalesce 将 null 值聚合为0：
select id, name, sum(coalesce(amount1, 0)) as amount1, sum(coalesce(amount2, 0)) as amount2, sum(coalesce(amount3, 0)) as amount3from yourtablegroup by id, name
我理解你有一个 case when，你问题中的输入是那个查询的结果。在不了解更多关于你的表的情况下，我最好的建议是将上面的查询中的 yourtable 替换为 (<yourquery>) t，当然，你需要将 <yourquery> 替换为你实际的查询。
查看这个示例：SQL Fiddle链接
测试数据库：
create table yourtable(id int,name varchar(8),amount1 int,amount2 int,amount3 int);insert into yourtable(id, name, amount1, amount2, amount3)values(1, 'abc', 10, null, null),(1, 'abc', null, 5, null),(1, 'abc', null, null, 40),(2, 'aaa', 90, 20, 10);
请注意，这里只提供了翻译的部分，没有其他内容。
英文:
You can aggregate using group by and coalesce null values to 0:
select id, name, sum(coalesce(amount1, 0)) as amount1, sum(coalesce(amount2, 0)) as amount2, sum(coalesce(amount3, 0)) as amount3from yourtablegroup by id, name
I understand you have a case when and your input in the question is the result of that. Without knowing more about your tables, the best I can suggest is to replace yourtable in the query above with (<yourquery>) t and of course, you need to replace <yourquery> with your actual query.
See this fiddle: http://sqlfiddle.com/#!15/e568d0/6
Test DB:
create table yourtable(id int,name varchar(8),amount1 int,amount2 int,amount3 int);insert into yourtable(id, name, amount1, amount2, amount3)values(1, 'abc', 10, null, null),(1, 'abc', null, 5, null),(1, 'abc', null, null, 40),(2, 'aaa', 90, 20, 10);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论