SQL query to get the number of appearances of an integer in the values ​​of the columns (or tips to do it in the code)

advertisements

The title is a little confusing but I couldn't find a better one. here is what i want to do. I have a Table of this form:


Name | Gender | Answers

Tom | Male | 1,2,3

Kate | Female | 1,4

John | Male | 2,4

Maggy | Female | 1,3


I have 2 questions:

1) If there a query to get the number of integers appearing in the Answers column?

In our example:

Answer | Count

1 | 3

2 | 2

3 | 2

4 | 2

2)If the above is possible is there a way to breakdown by gender:

In our example:

Gender | Answer | Count

Male | 1 | 1

Male | 2 | 2

Male | 3 | 1

Female | 1 | 2

Female | 3 | 2

I hope I was clear.

I am using C#, and reading data from an excel sheet using an OleDB Connection.

If there is no way to do it via SQL Query, how to do it in C# code. (I use a Datatable to fill the data I read)

Thank you very much for any help


You should not store multiple values in a field, you should normalise the table using a separate table for the answers:

Person:
Id  Name   Gender
1   Tom    Male
2   Kate   Female
3   John   Male
4   Maggy  Female

Answer:
Id Answer
1  1
1  2
1  3
2  1
2  4
3  2
3  4
4  1
4  3

Now you can easily get the number of answers using count

selecct p.Name, count(*) as Cnt
from Person p
inner join Answer a on a.Id = p.Id
group by p.Name

You can just as easily group by gender and answer:

selecct p.Gender, a.Answer, count(*)
from Person p
inner join Answer a on a.Id = p.Id
group by p.Gender, a.Answer