SQL query to obtain counts from two tables based on values and field names

Question

I want to count the alerts of the candidates based on district.

Below is the district-wise alert lookup table

Table_LKP_AlertMastInfo

DistrictID             FieldName              AlertOptionValue  
  71                    AreYouMarried                 Yes
  71                      Gender                      Female
  72                    AreYouMarried                 Yes

The above Table_LKP_AlertMastInfo FieldName should compare with table_RegistrationInfo fields to check the AlertOptionValue to get counts.

Below is the candidate details table:

Table_RegistrationInfo

CandidateId    DistrictID     AreYouMarried     Gender  
 Can001            71             Yes            Female
 Can002            71             No             Female
 Can003            72             Yes            Man  
 Can004            72             No             Man    

I want output like below:

Can001   2
Can002   1
Can003   1

Explanation of the above output counts:

Can001 have selected AreYouMarried:Yes and Gender:Female then count value 2
Can002 have selected  Gender:Female then count value   1
Can003 have selected AreYouMarried:Yes then count value   1
Can004 have not alerts 

Answer 1

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.