QUERY: split field by comma, display totals

[williamsba]

I have a field in my database that contains multiple values seperated by a comma:

ex: value1, value2, value3, value4, value5

This field could contain one value or more. I am trying to break the values apart by the comma, and then tally up the total of entries for that particular item in the database.

ex:
Record 1: value1, value3, value5
Record 2: value1, value2, value3
Record 3: value1, value4, value5

Totals (returned from DB):
value1 = 3
value2 = 1
value3 = 2
value4 = 1
value5 = 2

I need to do this within the query, or a stored procedure. Thanks!

Brad Williams
Webmaster
2d Force Service Support Group
United States Marine Corps

 

[SQLSister]

Is redesigning the structure a possibility? You can do what you are asking but you will never be able to do it efficiently. The first rule of database design is to NEVER store more than one piece of information in a field.

This is a case where you really should have a related table to store the values in. Then this is a simple matter of doing a count.

If you can't redesign, you will probably need to use Charindex or patindex and the left, right or substring functions.

 

[williamsba]

I was afraid that would be the answer. I can reformat the DB, but it is a highly used, very public table so it is a bit of a pain. I guess I'll do that.

Brad Williams
Webmaster