CODEWITHSUNDEEP
sql
phalondon
phalondon

Reputation: 305

How to group Material and Company in this Query

i have a table like this:

Material   Company    vendor  Salepersonofvendor

A               100           Coca        Peter
A               100           Coca        Rex
B               100           Coca        Vena
B               100           Pepsi       Weter
C               200           Samsung    Indi
D               200          Samsung    Thomax

Now i want to group by Material, company and vendor. if a material of a company has more than 1 vendor, then it will shows me "N/A" like this

Material   Company    vendor 
A               100           Coca       
B               100           N/A       
C               200           Samsung 
D               200          Samsung

Can you please help me how to to it?

Thanks

WITH CTE_Material AS (
       SELECT
             Material
             ,Company
             ,Vendor
             ,Saleperson
       FROM (VALUES
             ('A', '100','Coca','Peter')
             , ('A', '100','Coca','Rex')
             , ('B', '100','Coca','Vena')
             ,('B', '100','Pepsi','Weter')
             ,('C', '200','Samsung','Indi')
             ,('D', '200','Samsung','Thomax')
             ) AS Material( Material,Company,Vendor,Saleperson)
)

select * from CTE_Material

Upvotes: 2

Views: 34

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42753

Try:

select  Material,Company, case when count(distinct Vendor) > 1 then 'N/A' else max(Vendor) end as vendor
from CTE_Material
group by  Material,Company

Upvotes: 2

Related Questions