Apply search criteria on dynamic field table without using grouping or pivoting

Question

I have following two tables, one table is of person and another table is for storing various dynamic properties/information about the person.

 Id | Persons               PersonId | Field  | Value         
----+-------------         ----------+--------+-----------
 1  | Peter                      1   | City   | New York 
 2  | Jane                       1   | Age    | 26
                                 2   | City   | New York
                                 2   | Age    | 50

1. Can I apply a search condition in an sql query like person with age > 25 and city = 'New York' without grouping or pivoting the table.
2. What is best way to apply the search criteria with least performance overhead.

Answer 1

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'New York' 

Update

I did some tests and INNER JOIN looks fast enough. Here result and test script

SET NOCOUNT ON 
SET STATISTICS IO ON

CREATE TABLE KeyValue (
    ID INT NOT NULL IDENTITY CONSTRAINT [PK_KeyValue] PRIMARY KEY CLUSTERED
    ,PersonId INT NOT NULL
    ,Field varchar(30) NOT NULL
    ,Value varchar(255) NOT NULL
    ,CONSTRAINT UQ__KeyValue__PersonId_Field UNIQUE (PersonId, Field)
)
GO
--INSERT INTO KeyValue 500K "users", 4 "Fields" - 2M rows

CREATE NONCLUSTERED INDEX [IX__KeyValue__Field_Value_ID]
ON [dbo].[KeyValue] ([Field],[Value]) INCLUDE ([PersonId])
GO

select PersonId from (
    select PersonId, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY PersonId) RowNumber from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = '[email protected]' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
    ) x
) y where RowNumber = 4
--Table 'KeyValue'. Scan count 20, logical reads 1510, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

select PersonId from (
        select PersonId from KeyValue where [Field] = 'Age' and [Value] > 25 union all
        select PersonId from KeyValue where [Field] = 'City' and [Value] = 'Sydney' union all
        select PersonId from KeyValue where [Field] = 'Email' and [Value] = '[email protected]' union all
        select PersonId from KeyValue where [Field] = 'Name' and [Value] = 'UserName' 
) x GROUP by PersonId
HAVING COUNT(*) = 4
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'KeyValue'. Scan count 4, logical reads 1377, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT key1.PersonId 
FROM KeyValue key1
    INNER JOIN KeyValue key2 ON key1.PersonId = key2.PersonId
    INNER JOIN KeyValue key3 ON key1.PersonId = key3.PersonId
    INNER JOIN KeyValue key4 ON key1.PersonId = key4.PersonId
WHERE key1.[Field] = 'Age' and key1.[Value] > 25
    AND key2.[Field] = 'City' and key2.[Value] = 'Sydney'
    AND key3.[Field] = 'Email' and key3.[Value] = '[email protected]' 
    AND key4.[Field] = 'Name' and key4.[Value] = 'UserName' 
-- Table 'KeyValue'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO OFF
GO