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
 
    