This won't be possible without dynamic SQL if your data is modeled like it is, i.e. key-value pairs in Table_LKP_AlertMastInfo and columns in Table_RegistrationInfo. So with that out of our way, let's do it. Full code to the stored procedure providing the exact results you need is at the end, I'll follow with the explanation on what it does.
Because the alerts are specified as key-value pairs (field name - field value), we'll first need to get the candidate data in the same format. UNPIVOT can fix this right up, if we can get it the list of the fields. Had we only had only the two fields you mention in the question, it would be rather easy, something like:
SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (AreYouMarried, Gender)) upvt
Of course that's not the case, so we'll need to dynamically select the list of the fields we're interested in and provide that. Since you're on 2008 R2, STRING_AGG is not yet available, so we'll use the XML trick to aggregate all the fields into a single string and provide it to the query above.
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CONCAT('SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (',
STUFF((
SELECT DISTINCT ',' + ami.FieldName
FROM Table_LKP_AlertMastInfo ami
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), ')) upvt')
PRINT @sql
This produces almost the exact output as the query I wrote. Next, we need to store this data somewhere. Temporary tables to the rescue. Let's create one and insert into it using this dynamic SQL.
CREATE TABLE #candidateFields
(
CandidateID VARCHAR(50),
DistrictID INT,
FieldName NVARCHAR(200),
FieldValue NVARCHAR(1000)
);
INSERT INTO #candidateFields
EXEC sp_executesql @sql
-- (8 rows affected)
-- We could index this for good measure
CREATE UNIQUE CLUSTERED INDEX uxc#candidateFields on #candidateFields
(
CandidateId, DistrictId, FieldName, FieldValue
);
Great, with that out of the way, we now have both data sets - alerts and candidate data - in the same format. It's a matter of joining to find matches between both:
SELECT cf.CandidateID, COUNT(*) AS matches
FROM #candidateFields cf
INNER
JOIN Table_LKP_AlertMastInfo alerts
ON alerts.DistrictID = cf.DistrictID
AND alerts.FieldName = cf.FieldName
AND alerts.AlertOptionValue = cf.FieldValue
GROUP BY cf.CandidateID
Provides the desired output for the sample data:
CandidateID matches
-------------------------------------------------- -----------
Can001 2
Can002 1
Can003 1
(3 rows affected)
So we can stitch all that together now to form a reusable stored procedure:
CREATE PROCEDURE dbo.findMatches
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CONCAT('SELECT CandidateId, DistrictID
, FieldName
, FieldValue
FROM Table_RegistrationInfo t
UNPIVOT (FieldValue FOR FieldName IN (',
STUFF((
SELECT DISTINCT ',' + ami.FieldName
FROM Table_LKP_AlertMastInfo ami
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), ')) upvt')
CREATE TABLE #candidateFields
(
CandidateID VARCHAR(50),
DistrictID INT,
FieldName NVARCHAR(200),
FieldValue NVARCHAR(1000)
);
INSERT INTO #candidateFields
EXEC sp_executesql @sql
CREATE UNIQUE CLUSTERED INDEX uxc#candidateFields on #candidateFields
(
CandidateId, DistrictId, FieldName
);
SELECT cf.CandidateID, COUNT(*) AS matches
FROM #candidateFields cf
JOIN Table_LKP_AlertMastInfo alerts
ON alerts.DistrictID = cf.DistrictID
AND alerts.FieldName = cf.FieldName
AND alerts.AlertOptionValue = cf.FieldValue
GROUP BY cf.CandidateID
END;
Execute with
EXEC dbo.findMatches
You'd of course need to adjust types and probably add a bunch of other things here, like error handling, but this should get you started on the right path. You'll want a covering index on that alert table and it should be pretty fast even with a lot of records.
