Recently one of my friend had asked me a query by which he could get all such vendors
who sell all the ItemTypes present in the table.Of course for sake of illustration purpose I have de normalised the table as my blog is not for sake of teaching the fundamentals of database but rather to provide tweaks for non trivial problems a developer faces.
Here is the table structure

In the above condition the o/p should be vendor id 1 and 2
To my surprise on first glance I found the pivot query working much faster than other queries.
Will post more about the performance implications later.But as for now here is the query.

declare @tbl as table(vendor int,ItemType varchar(40))

insert into @tbl
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 3,'c'

select * from @tbl

--Method 1
select vendor from
(
select vendor,ItemType from @tbl
)u
pivot
(max(ItemType) for ItemType in ([a],[b],[c]))v
where a is not null and b is not null and c is not null

--Method 2
select vendor from @tbl where ItemType in('a','b','c')
group by vendor
having COUNT(case ItemType when 3 then 1 else 0 end)=3

--Method 3
select distinct vendor from
(
select vendor,(select COUNT(distinct ItemType)as rid from @tbl t2 where t1.vendor=t2.vendor and t2.ItemType in ('a','b','c'))as rid
from @tbl t1
)T where T.rid=3

--Method 4
select vendor from @tbl where ItemType in ('a','b','c')
group by vendor
having COUNT(distinct ItemType)=3