Dynamic SQL Problem

Transact-SQL
#1

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?

Martin

#2

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
#3

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

#4 
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)
;
1 Like
#5

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
#6

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,

Martin