How to multi-select filter an EAV table in SQL Server

Question

I have an EAV table with attributes and would like to do a hybrid selection of the items based on variables that are passed into a stored procedure.

Sample table:

| group_id | item_id | key    | value |
+----------+---------+--------+-------+
| 1        | AA      | length | 10    |
| 1        | AA      | width  | 10    | 
| 1        | AA      | color  | white |
| 1        | AA      | brand  | beta  |
| 1        | BB      | length | 25    |
| 1        | BB      | brand  | alpha |
| 2        | CC      | brand  | alpha |

Sample query:

declare @attributes nvarchar(max) = 'brand name, length'
declare @attributeValues nvarchar(max) = 'alpha, beta, 25'
declare @id int = 1

select *
into #allProductsFromGroup
from items
where group_id = @id

select item_id
from #allProductsFromGroup #all
where [key] in (select value from string_split(@attributes, ',')) 
  and [value] in (select value from string_split(@attributeValues, ','))

Expected output:

| item_id |
+---------+
| BB      |

I could hard-code in and and or statements for each key, but there are many, and I am looking for a more scalable solution.

Passing in and parsing JSON would be good, like:

[
  { "brand": "aplha" },
  { "brand": "beta" },
  { "length": 25 }
]

How can I write the second select to dynamically return a subset of allProductsFromGroup that dynamically include multiple results from the same group (multi-select brand or multi-select length), but exclude from other groups (color, length, etc.)?

Answer 1

The target query might look something like this:

with q as
(
    select item_id,
       max( case when [key] = 'brand' then [value] end ) brand,
       max( case when [key] = 'length' then cast( [value] as int ) end ) length,
    from #allProductsFromGroup 
    group by Item_id
)
select item_id
from q
where brand in ('alpha','beta') and length=25

You just have to build it from the incoming data (yuck). A simpler query form to generate might be something like

select item_id
from #allProductsFromGroup 
where [key] = 'brand' and [value] in ('alpha','beta')
intersect 
select item_id
from #allProductsFromGroup 
where [key] = 'length' and [value] = 25

mapping and criteria to intersect, and or criteria to union. It's likely to be cheaper too, as each query can seek an index on (key,value).