How to group single rows in one?

Question

Good day. I have a table with columns: year, item, count. Let's say I have the following rows in db table:

2007 item1 value1
2007 item2 value2
2008 item1 value3
2008 item2 value4
2009 item1 value5
2009 item2 value6

Is it possible with 1 query to have a result like this:

2007 value1 value2
2008 value3 value4
2009 value5 value6

I know how to group and so on, the problem is that the number of items could be different for each year, so, let's say we have 5 items total, I need the following output (if not found, add 0) :

2007 value1 value2 0 0      0  
2008 0      value3 0 value4 0

Does this answer your question? [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) — Arun Palanisamy, Dec 23 '20 at 08:28
Arun Palanisamy no, please review my question once again. The problem is that each row in output should contain all values in the same order, or 0 if not present. — Natalie, Dec 23 '20 at 08:53
Why is that `0` in first column for `2008`? How are you ordering this? — Arun Palanisamy, Dec 23 '20 at 09:15
Please read my second example, skip the first part with 2 items only. It was not informative. In other words, I have 5 items for sale , each item may have some sales data for some year or may not . I need an array for each year, where all of them will be present in the same order, even if no row present in database. — Natalie, Dec 23 '20 at 09:33

score 0 · Answer 1 · answered Dec 23 '20 at 10:23

You seem to want something along these lines:

select year
    coalesce(sum(value) filter(where item = 'item1'), 0) as value1,
    coalesce(sum(value) filter(where item = 'item2'), 0) as value2,
    coalesce(sum(value) filter(where item = 'item3'), 0) as value3,
    coalesce(sum(value) filter(where item = 'item4'), 0) as value4
from mytable
group by year

This assumes that value is of a numeric datatype.

How to group single rows in one?

1 Answers1