Using case when Referring to other Column

Transact-SQL
1

Hi, I am trying to resolve a problem using SQL and I am getting stuck.
Here is the situation
I have two fields
Item ID Seller
10345 Marketplace
10345. Independent
10345 Others
10223 Independent
10224 Marketplace

Here Item ID is ID for an Item sold at a store and Seller is the type of Seller for this Item.
We have 3 Sellers - Marketplace, Independent and Others.
An Item can have all 3 types of sellers or 2 of them or just one.
The question is, If an item is sold by all the 3 types of sellers or 2 types of sellers I want it be consolidated saying 'Shared' else if an Item has just 1 seller type return the Seller Type.

Your help is highly appreciated. Thank you

2 

SELECT [Item ID], CASE WHEN COUNT(DISTINCT Seller) = 1 
    THEN MAX(Seller) ELSE 'Shared' END AS [Seller Type]
FROM dbo.table_name
GROUP BY [Item ID]
2 Likes
3

hi

this is another way to do this

i am doing this for my stats , my timing , my performance ( cognition , meta cognition etc etc )
my tracking for myself .. my noodles

it may help performance wise

; with cte as 
    (  select Item_ID ,  min(seller) as mins  , max(seller ) as maxs from #MyTable group by Item_ID ) 
select 
      Item_ID 
    , case when mins = maxs then Mins else 'Shared' end  
from 
   cte

image

1 Like
4

If you prefer, you could do this:


SELECT [Item ID], CASE WHEN MIN(Seller) <> MAX(Seller) 
    THEN 'Shared' ELSE MAX(Seller) END AS [Seller Type]
FROM dbo.table_name
GROUP BY [Item ID]
5

Just want to advice another possible solution. Using STRING_AGG you can return All Sellers in one row

SELECT ItemID, STRING_AGG(Seller, ',') WITHIN GROUP (ORDER BY Seller)
FROM Sales
GROUP BY ItemID

-- Look live example below
https://sqlize.online/sql/mssql2019/92bcfdca37bac02ce71f1e269529cba8/
6

Unfortunately that doesn't meet the needs of the poster.

8

Hi Thanks for the solution .
I have a question,apart from trying to avoid the case function to be grouped finally, why are you using max function here where as we can just return the seller type ?

9

Thank you for your response, this is helpful

10

hi

create sample data script

drop table if exists #temp

create table #temp ( ItemID int , Seller varchar(20))
insert into #temp select 10345,'Marketplace'
insert into #temp select 10345,'Independent'
insert into #temp select 10345,'Others'
insert into #temp select 10223,'Independent'
insert into #temp select 10224,'Marketplace'

Scott is using max because thats the way the SQL will work and give the correct output

image
image1206×377 12.5 KB

image
image1127×329 13.9 KB