0

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

astentx
  • 6,393
  • 2
  • 16
  • 25
c_n_blue
  • 182
  • 2
  • 10
  • 2
    Don't use a windowed aggregate, use a non-windowed aggregate and a `GROUP BY`. There aren't "duplicate rows", you have 1 row per row in your table. – Thom A Jul 20 '22 at 11:22
  • Can't debug a query **fragment** – SMor Jul 20 '22 at 11:24
  • @Larnu So i changed my case statement to read CASE WHEN state = x THEN MIN(date) ELSE NULL and grouped by 1,2,state and I still get the multiple rows – c_n_blue Jul 20 '22 at 11:31
  • 1
    `GROUP BY` doesn't accept ordinal positions in SQL Server, @c_n_blue ; I would expect an error. Though your aggregate should be wrapped around the entire `CASE` expression, and you shouldn't be grouping on it. – Thom A Jul 20 '22 at 11:33
  • OHHH, so to read MIN (CASE ) . GReat, did this and it worked. Thank you – c_n_blue Jul 20 '22 at 11:34
  • 1
    @c_n_blue Would you mind posting the working code as an answer? It's OK on Stack Overflow to answer your own question. – Gert Arnold Jul 20 '22 at 13:34
  • 1
    Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – astentx Jul 20 '22 at 14:53

0 Answers0