How to find out items sold by one or multiple Sellers using indicator flag?

Database

How to find out items sold by one or multiple Sellers using indicator flag?

Hi,

There are 3 tables ITEM(Item_id, Item_title), SELLER(Seller_id,Seller_name), ITEMSELLER(Item_id,Seller_id), where i have to findout the Items that are sold by only one seller, multiple seller using an indicator flag.

Ex Input:-

Item_Id   Seller_id

A         Walmart

A         Kroger

B         WholeFoods

C         Walmart

D          Kroger

E          Wholefoods

E          Walmart

Required Output:-

Flag:- only Walmart -W, only Kroger-K, only WholeFoods-WF, Walmart&Kroger-WK, Walmart&WholeFoods- WWF, Kroger& WholeFoods- KWF, all three WKWF.

Item_Id   Seller_id     Flag

A         Walmart        WK

A         Kroger         WK

B         WholeFoods     WF

C         Walmart        W

D          Kroger        K

E          Wholefoods    WWF

E          Walmart       WWF

Please help me on how to acheive this, we are using TD14.

Thanks,

Chava.




1 REPLY

Re: How to find out items sold by one or multiple Sellers using indicator flag?

1) SELECT

ITEM_ID,SELLER_ID,

MAX(CASE WHEN rn = 1 THEN 'K' END)

  || MAX(CASE WHEN rn = 2 THEN 'W'  ELSE '' END)

  || MAX(CASE WHEN rn = 3 THEN 'WF'  ELSE '' END)

 END)

FROM

(SELECT ITEM_ID,SELLER_ID, ROW_NUMBER()OVER(PARTITION BY ITEM_ID ORDER BY SELLER_ID ASC) AS RN FROM ITEMSELLER)A

GROUP BY 1,2;

2) UPDATE KW TO WK AND KWWF TO WKWF , as i think they will come in correct order.