I have a table that looks like:
consumer_id | product_id | prod_state | date |
---|---|---|---|
a | 1 | wholesale | 1 |
a | 1 | stock | 2 |
a | 1 | sold | 3 |
a | 1 | return | 4 |
I want to transform it such that my states are in the columns, and the value is the date, i.e something like
consumer_id | product_id | wholesale_date | stock_date | sold_date | return_date |
---|---|---|---|---|---|
a | 1 | 1 | 2 | 3 | 4 |
(the dates are actual dates, but to create the table easily, I've just put numbers).
So in order to do this, I write the following code
SELECT consumer_id
, product_id
, CASE WHEN prod_state = 'wholesale'
THEN MIN(date) OVER(PARTITION BY consumer_id, product_id, prod_state)
ELSE NULL
END as wholesale_date
, CASE WHEN prod_state = 'stock'
THEN MIN(date) OVER(PARTITION BY consumer_id, product_id, prod_state)
ELSE NULL
END as sold_date
, CASE WHEN prod_state = 'wholesale'
THEN MIN(date) OVER(PARTITION BY consumer_id, product_id, prod_state)
ELSE NULL
END as sold_date
from table
With a similar case statement used for each state.
However what this ends up doing is creating a table that looks a bit like this
consumer_id | product_id | wholesale_date | stock_date | sold_date | return_date |
---|---|---|---|---|---|
a | 1 | 1 | NULL | NULL | NULL |
a | 1 | NULL | 2 | NULL | NULL |
a | 1 | NULL | NULL | 3 | NULL |
a | 1 | NULL | NULL | NULL | 4 |
How do I remove all the duplicate rows and collapse them all into one row?
Even by adding group by's to the bottom of my query doesn't help with this.
Thank you