SQL Compare tables and Return a Bit Array or CSV

This is a tough one! I have two tables tblPeopleAnswers and tblAnswers

tblPeopleAnswers

PersonID QuestionID AnswerID
15       5          1
15       5          3
17       5          1
17       5          2

tblAnswers

QuestionID AnswerID
5          1
5          2
5          3

I would like to loop through each PersonID and return a bit array or csv of their a开发者_Python百科nswers compared to the available answers. So for the example above PersonID - 15 Bit array- 101, PersonID - 17 Bit array - 110.

OutputTable

PersonID QuestionID BitAnswer
15       5          101
17       5          110

I need this all done in SQL Server 2008.

I have at least 1000 people, 1000 questions and 5 answers per question, so I need some sort of speed too.

Possibly the term you're looking for to describe what you want is "bitmask".

This works by building a list of all possible combinations of people and answers, joining that to the actual answers, then using a recursive CTE to concatenate the results into a single row:

DECLARE @tblPeopleAnswers TABLE
(PersonID INT 
,QuestionID INT
,AnswerID INT
)

INSERT @tblPeopleAnswers
VALUES (15,5,1),
(15, 5, 3),
(17, 5, 1),
(17, 5, 2)

DECLARE @tblAnswers TABLE
(QuestionID INT
,AnswerID INT
)

INSERT @tblAnswers
VALUES
(5,1),
(5,2),
(5,3)

;WITH ansCTE
AS
(
    SELECT answers.PersonID,
            answers.QuestionId,
            answers.AnswerId,
            CASE WHEN tpa.PersonID IS NULL 
                THEN '0'
                ELSE '1'
            END AS RESULT,
            ROW_NUMBER() OVER (PARTITION BY answers.PersonId, answers.QuestionId
                                ORDER BY    answers.AnswerId DESC
                                ) AS rn

    FROM (SELECT * FROM 
            (SELECT DISTINCT PersonID FROM @tblPeopleAnswers) AS z -- you may have @tblPeople you could use here??
            CROSS JOIN @tblAnswers
            ) AS answers
    LEFT JOIN @tblPeopleAnswers AS tpa
    ON tpa.QuestionID = answers.QuestionID
    AND tpa.AnswerId = answers.AnswerId
    AND tpa.PersonID = answers.PersonID
)
,recCTE
AS
(
    SELECT PersonId,
            QuestionId,
            AnswerId,
            CAST (RESULT AS VARCHAR(MAX)) AS BitAnswer,
            rn
    FROM ansCTE WHERE AnswerID = 1
    UNION ALL
    SELECT r.PersonId,
           r.QuestionId,
           a.AnswerID,
           r.BitAnswer + CAST(a.Result AS CHAR(1)),
           a.rn
    FROM recCTE AS r
    JOIN ansCTE AS a
    ON   a.PersonID = r.PersonID
    AND  a.QuestionID = r.QuestionID
    AND  a.AnswerID = r.AnswerID + 1
)
SELECT PersonId, 
       QuestionId,
       BitAnswer
FROM recCTE
WHERE rn = 1
ORDER BY PersonID, QuestionID