SQL Pivot column values

Question

I have tried following this and this(SQL Server specific solution) but were not helpful.

I have two tables, Product and Sale and I want to find how many products are sold on each day. But I want to pivot the table so that columns become the products name and each row will contain the amount of products sold for each day ordered by the day.

Simplified schema is as following

CREATE TABLE product (
    id integer,
    name varchar(40),
    price float(2)
);

CREATE TABLE sale(
  id integer,  
  product_id integer,
  transaction_time timestamp
);

This is what I want

I only managed to aggregate the total sales per day per product but I am not able to pivot the product names.

select date(sale.transaction_date)
  , product.id
  , product.name
  , count(product_id)
from sale inner join 
  product on sale.product_id = product.id
group by date(sale.transaction_date)
  , product.id
  , product.name

This is the situation so far

Please suggest.

score 0 · Answer 1 · answered Oct 16 '19 at 04:52

0

You need pivoting logic, e.g.

select
    s.transaction_date::date,
    count(case when p.name = 'intelligent_rubber_clock' then 1 end) as intelligent_rubber_clock,
    count(case when p.name = 'intelligent_iron_wallet' then 1 end) as intelligent_iron_wallet,
    count(case when p.name = 'practical_marble_car' then 1 end) as practical_marble_car
from sale s
inner join product p
    on s.product_id = p.id
group by
    s.transaction_date::date;

Since your expected output aggregates by date alone, then only the transaction date should be in your GROUP BY clause. The trick used here is to take the count of a CASE expression which returns 1 when the record is from a given product, and 0 otherwise. This generates conditional counts for each product, all in separate columns. To add more columns, just add more conditional counts.

answered Oct 16 '19 at 04:52

Tim Biegeleisen

502,043
27
286
360

Thanks for the prompt reply but the product names are quite a lot so ultimately it will become a very wide table. Adding conditional statements for each product seems quite difficult. – Ali Oct 16 '19 at 05:11
1

Then look into using cross-tab with Postgres. I have not done much work with cross tabs, so I can't give you a definite answer. – Tim Biegeleisen Oct 16 '19 at 05:17
1

@Ali . . . Generate the code using a spreadsheet or a query on the information_schema tables. – Gordon Linoff Oct 16 '19 at 12:12

SQL Pivot column values

1 Answers1