CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
topher
topher

Reputation: 14860

Combine sql rows into additional columns

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?

Upvotes: 0

Views: 64

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656784

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:

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
;

Upvotes: 3

Related Questions