CODEWITHSUNDEEP
sqlsql-servert-sql
Lawraoke
Lawraoke

Reputation: 556

Sql Query Merging column with same value

The scenario is below:

There were both product named 'A' and B which sold previous time. Nowadays, this product already combined and renamed as 'C'

Let's say for example:

CompanyName  ProductName  PurchaseDate  ExpiredDate
CompanyA         A           2016          2017
CompanyA         B           2017          2018
CompanyA         c           2020          2021

A same company brought the product overtime, the year state as behind is the purchased date and expired date for the specific product

However, now product A and B is no longer be sold, which combined into product 'C'. So for report displaying, the new product will always be shown, which is C in this case. I try query as below

SELECT * FROM Product CASE WHEN ProductName IN ('A' and 'B') THEN 'C' ELSE ProductName END ProductName

However, after performing the above action, the past record of A and B already change to C and it will appear duplicate result as follow:

CompanyName  ProductName  PurchaseDate  ExpiredDate
CompanyA         C           2016          2017
CompanyA         C           2017          2018
CompanyA         c           2020          2021

So I am thinking that, is that a way for merge those rows? and the result may look like this

CompanyA C 2016 2021

If I am not wrong, after performing the merging, I have to deal with MIN and MAX

Upvotes: 0

Views: 112

Answers (2)

AziMez
AziMez

Reputation: 2072

A few Edit on Gordon Linoff suggestion.

select CompanyName  ,(case when ProductName in ('A', 'B') then 'C' else ProductName end) as ProductName,
       min(PurchaseDate  ) as PurchaseDate   , max(ExpiredDate) as ExpiredDate
from product
group by (case when ProductName in ('A', 'B') then 'C' else ProductName end) ;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use aggregation with a case expression:

select (case when productname in ('A', 'B') then 'C' else productname end) as productname,
       min(startyear), max(endyear)
from product
group by (case when productname in ('A', 'B') then 'C' else productname end)

Upvotes: 2

Related Questions