I have this simplified table that describes all features of the Items in a db. In my case I have more than 300'000 rows describing 150'000 different Items. There are more than 800 different features.
ItemID Feature Category
-----------------------------------
ItemA Blue Color
ItemA Steal Material
ItemA 100 Length
ItemB Red Color
ItemC Steal Material
An Item can have up to features, but could have also have just one.
Question: How do I get all Items that have the same features as ItemA?
I was trying to solve this with dynamic SQL:
declare @Features nvarchar(max) = ''
set @Features += QUOTENAME(Feature) + ',' from Table
set @Features = left(@Features, len(@Features)-1)
declare @sql nvarchar(max)
set @sql = 'Select * from (Select ItemID, Feature from Table)a Pivot ( count(Feature) for Feature in (' + @Features + ')) as pvt'
exec sp_executesql
Why is ItemB returned? None of it's features is in the @Features string????
How can I force that only Items are returned were all features match @Features?
with cte
as (select itemid
,feature
,category
,sum(1) over(partition by itemid) as c
from yourtable
where itemid='ItemA'
)
select b.itemid
from cte as a
inner join yourtable as b
on b.category=a.category
and b.feature=a.feature
and b.itemid!=a.itemid
group by b.itemid
having count(distinct b.category+b.feature)=avg(a.c)
;
declare @ItemID varchar(50)
set @ItemID = 'ItemA' /*returns no results*/
--set @ItemID = 'ItemC' /*returns ItemA*/
select search.ItemID, count(*)
from #data main
inner join #data search on search.ItemID <> main.ItemID AND search.Feature = main.Feature
where main.ItemID = @ItemID
group by search.ItemID
having count(*) = (select count(feature) from #data where ItemID = @ItemID)
Great! I implemented Scotts suggestion which is about the same as bitsmed and it works with good performance. It really didn't need Dynamical SQL; but I was convinced I would need to split the features in seperate columns.
@yosiasz: you are right; I should post the scripting of the sample data. I will do it in future posts. Thank you for pointing this out,