SQL Query Issues

1

I have Table name Buyers having three Columns Sr_No , Buyer_Id, Buyer_Status.
I want to make a new column having name as Final_Uniques_Status where I want to capture "Yes" when I have atleast one "Yes" in Buyer_Status Column corresponding to the Buyer_Id, and "No" when there is not a single "Yes" in Buyer_Status column corresponding to the Buyer_Id. For example : Buyer_Id 111 has atleast one "Yes" in its Buyer_Status column so in Final_Uniques_Status Column Yes get captured across all Sr_Nos which are related to Buyer_Id 111. So how can I do that in SQL Query in select statement
QA

2

hi

this is one way of several ways to do this

Hope this helps

create table SampleData 
(
SrNo int ,
BuyerId int , 
BuyerStatus varchar(100)
)

insert into SampleData select 1,111,'Yes'
insert into SampleData select 2,111,'No'
insert into SampleData select 3,111,'No'
insert into SampleData select 4,111,'No'
insert into SampleData select 5,111,'No'

insert into SampleData select 6,222,'No'
insert into SampleData select 7,222,'No'
insert into SampleData select 8,222,'No'
insert into SampleData select 9,222,'No'

insert into SampleData select 10,444,'Yes'
insert into SampleData select 11,444,'Yes'


select 'SampleData',* from SampleData

; with cte as 
(
select distinct BuyerId from SampleData where BuyerStatus = 'Yes' 
)
select a.SrNo , a.BuyerId, case when B.BuyerId is not null then 'Yes' Else 'NO' end as BuyerStatus from  SampleData a left join cte b on a.BuyerId = b.BuyerId 

drop table SampleData

image
image315×542 5.44 KB

3

another simpler much shorter way

select 
        SrNo
    ,   BuyerID
    ,   BuyerStatus
    ,   Max(BuyerStatus) over(partition by BuyerId)  as FinalUniqueStatus 
from 
   SampleData

image

4

Thanks @harishgg1 ,it worked for me....