I have a lookup table and record batch Table I would like to do a count on the records stored in that batch by counting the number of records in that batch that have  a certain key on the responseKey column. This column keys are stored in the look up table for comparison. So I would like to do a join sql pivot that will use my keys stored in the lookup as columns and count as values

so far I have managed to do this but this is not efficient I would like to fire that sql once not on every count

SELECT * FROM
(
SELECT tMatch.DataValue, Count(*)
FROM ResponseRMAP.Record bRecord
JOIN Match ON tMatch.TableName = 'Match' AND tMatch.KeyName = bRecord.ResponseCode
WHERE bRecord.%ParentBatch = 55

)