Combine two rows in table using one column data

Question

I have a table where I want to combine two separate rows into one row. This is a product catalog that is storing information on separate rows. Here is the sample data and the expected results.

Table name: ProductCatalog

Product_ID  | Action     | Date
-----------------------------------------
0001        | Added      | 12/11/1983
0001        | Removed    | 01/01/2003
0002        | Added      | 12/11/1983

Expected result:

Product_ID  | Added        | Removed
========================================
0001        | 12/11/1983   | 01/01/2003 
0002        | 12/11/1983   | null

I have tried joining on Product_ID to get Added and Removed dates to be side by side in a new table or view but I don't get the desire results. I am not using MAX(column) since I don't get the desire results or maybe I am grouping wrong.

Answer 1

I think the easiest way is conditional aggregation:

select pc.product_id,
       max(case when pc.action = 'Added' then pc.[date] end) as Added,
       max(case when pc.action = 'Removed' then pc.[date] end) as Removed
from ProductCatalog pc
group by pc.product_id;

You can also do this using pivot.

Answer 2

select * from
(select *from ProductCatalog)p
pivot(max(Date1) for  Action1 in ([Added],[Removed]))as pvt

Answer 3

First what you need to do is to get data into two separate columns, after than you can wrap that in sub-select and group by Product_id, because only one of the AddedDate or RemovedDate will have value we can use MAX function to display that data, producing only 1 row per Product_ID

SELECT Product_id
       ,MAX(AddedDate)
       ,MAX(RemovedDate)
    FROM (
           SELECT Product_ID
               ,CASE WHEN [ACTION] = 'Added' THEN [date]
                     ELSE NULL
                END AS AddedDate
               ,CASE WHEN [ACTION] = 'Removed' THEN [date]
                     ELSE NULL
                END AS RemovedDate
            FROM ProductCatalog
         ) a
    GROUP BY Product_id

Answer 4

Try something like (pseudo code)

Select a.ProductID, a.AddedDate, b.RemovedDate
  from table a, table b left outer join on a.ProductId = b.ProductID
 where a.tran = "Removed"