Identify row matches across multiple columns

Question

I have been given a table that is populated by '1' and '0' based on the yes/no answers to a survey. I have been asked to identify all totally distinct answers, i.e. People who answered 'Yes' to questions 1, 17, 23, 234 and 238. There are many columns (500+) and thus many answer permutations. Any ideas?

Answer 1

The specific numbers you mention are a bit confusing: are they just an example?

You are aware of the DISTINCT statement in sql? That, and correct selection of which columns you want, should solve your problem.

Byron's answer (now deleted: it had a select distinct with all 500 columns written out long hand) is good, and makes use of explicit column names, which is good practise in many cases. If you want shorter notation, your sql version may support Select Distinct *.

Answer 2

select count(distinct col_name) from table_name where answer = '1' and id in (1,17,23,234,238)

Answer 3

Employ your text editor or use your database tool to produce a list of the column names,

then just do this

select max(person_id)
from answer_table 
group by (
  a1,a2,a3,....  -- paste list of columns here.
  )
having count(
  a1,a2,a3....
  ) = 1; -- return only answer sets that have no duplicate

max(person_id) will pull out the single person's ID without breaking the GROUP BY.

Answer 4

The following statements assumes your input is 2,3 and column names are 2Q,3Q ...

DECLARE @QueryInput VARCHAR(100)
DECLARE @Query nVARCHAR(4000)
SET     @QueryInput = '2,3'
SET     @QueryInput = REPLACE(@QueryInput,',','=1 AND Q')
SET     @QueryInput = 'Q'+@QueryInput
SET     @QueryInput = @QueryInput+'=1'

PRINT   @QueryInput

SET     @Query = 'SELECT * FROM answer_table WHERE  '+@QueryInput
PRINT   @Query
EXEC SP_EXECUTESQL @Query