Dynamic SQL Problem

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

This returns:

ItemID    Blue    Steal   100
ItemA     1       1       1
ItemB     0       0       0
ItemC     0       1       0

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?


always provide sample data as ddl and dml

Notice the value of @Features

declare @sql nvarchar(max),  @Features nvarchar(max) = ''

create table #bizwax(ItemID varchar(50), Feature  varchar(50), 
Category  varchar(50))
insert into #bizwax
select 'ItemA',      'Blue ',       'Color	   ' union 
select 'ItemA',      'Steal',       'Material  ' union 
select 'ItemA',      '100  ',       'Length	   ' union 
select 'ItemB',      'Red  ',       'Color	   ' union 
select 'ItemC',      'Steal',       'Material  ' 

select distinct Feature from #bizwax

select distinct @Features += QUOTENAME(Feature) + ',' from #bizwax
set @Features = left(@Features, len(@Features)-1)

select @Features

set @sql = 'Select * from (Select ItemID, Feature from #bizwax)a 
Pivot ( count(Feature) for Feature in (' + @Features + ')) as pvt'
exec sp_executesql @sql

drop table #bizwax

is not a filter as in a where clause. you would have to add things in a where clause to filter out rows that do not match @Feature

with cte
  as (select itemid
            ,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)
1 Like

You don't really need dynamic SQL:

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)
1 Like

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,