PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to convert multiple rows into single row but with different column names in postgressql

My resultant out is this using case when in postgres

id   name    amount1      amount2       amount3
1     abc    10           null          null
1     abc    null         5             null
1     abc    null         null          40

I want my output as:

id   name    amount1      amount2       amount3
1    abc     10           5             40
2    aaa     90           20            10
Tags:

2

Answers


  1. 0

    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 amount3
from mytable
group by id
    Login or Signup to reply.
  2. 0

    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 amount3
from yourtable
group 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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search