SQL replace duplicate record with value based on condition

Question

I am trying to create a report that shows a single item(store_Product) purchased by store location(store_ID).

There are total 3 types of store_Product: product_A, product_B, and product_C. There are over a thousand unique store_ID.

My issue is that some unique store_ID have multiple store_Products and I need to shrink the query so it returns only unique store_IDs

These are the rules of condition:

• if a store has product_B and any other product, one unique store_ID record would have product_B as the store_Product value

• if a store has product_A and product_C, one unique store_ID record would have product_A as the store_Product value

• if a store has only one record then store_Product remains unchanged.

So the example below explains what I have on the left and what I want it to look like on the right:

I am using SQL server 2014 building the report in SSRS.

Really appreciate any help!

EDIT: What i have so far:

SELECT [store_ID]
  ,MIN(CASE WHEN store_product = 'product_B' THEN '1_product_B'
        WHEN store_product = 'product_A' THEN '2_product_A'
        ELSE '3_product_C' END) AS 'Prob_Group'
  ,CASE 
    WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '24'
        THEN '0 - 24 HRs'
    WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 >= '24'
        AND (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '48'
        THEN '24 - 48 HRs'
    WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 >= '48'
        AND (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '72'
        THEN '48 - 72 HRs'

        ELSE 'Over 168 HRs'

        END AS 'Hour_Range'
FROM myTable 
WHERE *filters*
GROUP BY [store_ID]
        ,CASE 
        WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '24'
            THEN '0 - 24 HRs'
        WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 >= '24'
            AND (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '48'
            THEN '24 - 48 HRs'
        WHEN (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 >= '48'
            AND (DATEDIFF(MINUTE, Opened_Time, GETDATE())) * 1.0 / 60 < '72'
            THEN '48 - 72 HRs'
            ELSE 'Over 168 HRs'
            END     
ORDER BY [store_ID]

Answer 1

I'm being very sneaky here:

SELECT store_id, 
       MIN( CASE WHEN store_Product = "product_B" THEN "1_product_B"
                 WHEN store_Product = "product_A" THEN "2_product_A"
                 ELSE "3_product_C" 
            END) as Product
FROM YourTable
GROUP BY store_id

So if the store has product_B, the result would be 1_product_B, not matter if are product_A and product_C.

Same if not product_B the minimum between 2_product_A and 3_product_C is 2_product_A also works if only has product_A.

After you have the result you can remove the first two characters

WITH cte as (
    SELECT store_id, 
           MIN( CASE WHEN store_Product = "product_B" THEN "1_product_B"
                     WHEN store_Product = "product_A" THEN "2_product_A"
                     ELSE "3_product_C" 
                END) as Product
    FROM yourTable
    GROUP BY store_id
)
SELECT store_id, RIGHT(Product, LEN(Product) - 2) AS fixProductName
FROM CTE