Combine sql rows into additional columns

Question

I have a database table 'stock_usage' that looks something like this.

id  |   item_id |   store_id    |   quantity    |   timestamp   
-------------------------------------------------------------

There is an item table and a store table. I'm creating a stock usage report in csv with the item in the first column and the rest representing a store each containing the total quantity used as below.

item_name   |   store_A |   store_B |   store_C |...
----------------------------------------------------
soap        |   3       |   4       |   5       |

My current solution is to retrieve the totals for each item per store as shown below and loop through the results in php to obtain the structure above.

item_name   |   store   |   quantity
------------------------------------
soap        |   store_A |   3
soap        |   store_B |   4
soap        |   store_C |   5

Is there a way to obtain this in sql without the additional code?

Please show your SQL code even if it doesn't work. And your version of Postgres. — Erwin Brandstetter, Dec 17 '13 at 19:39
@Robert the number varies, but I can restrict the number of stores. — topher, Dec 17 '13 at 19:42

Erwin Brandstetter · Accepted Answer · 2022-03-27T23:27:07.487

In Postgres, use crosstab() from the additional module tablefunc to pivot data. It's up for debate whether that's "native" or not.

Run once per database:

CREATE EXTENSION tablefunc;

Basics:

PostgreSQL Crosstab Query

However, what you are trying to do is the opposite of a pivot function!

In Postgres 9.4 or later use a VALUES expression in a LATERAL subquery to "unpivot":

SELECT s.item_name, t.*
FROM   stock_usage s
, LATERAL (
   VALUES
     ('store_A', s.store_a)
   , ('store_B', s.store_b)
   , ('store_C', s.store_c)
   ) t(year, count);

In Postgres 9.3 or older (original answer) use UNION ALL:

SELECT item_name, 'store_A'::text AS store, store_a AS quantity
FROM   stock_usage

UNION ALL
SELECT item_name, 'store_B'::text, store_b
FROM   stock_usage

UNION ALL
SELECT item_name, 'store_C'::text, store_c
FROM   stock_usage
;

Combine sql rows into additional columns

1 Answers1