SQL - Check for different records in single table

user_16248412 0
Viewed 21

How would you write a statement to check for each item and y/n or 1/0 for sites if the record exists.

Item site
ItemA SiteA
ItemA SiteB
ItemA SiteC
ItemB SiteB
ItemC SiteA

Exptected Results

|Item | CheckA| CheckB |CheckC|
|:---- | :----| :----| :----| 
|ItemA |Y| Y| Y|
|ItemB |N| Y| N |
|ItemC |N | N| Y|

Thank you all,

sql
1 Answers

Just use conditional aggregation:

select item,
       max(case when site = 'SiteA' then 'Y' else 'N' end) as CheckA,
       max(case when site = 'SiteB' then 'Y' else 'N' end) as CheckB,
       max(case when site = 'SiteC' then 'Y' else 'N' end) as CheckC
from t
group by item;
user_11440350
Related