SQL group by splitted data

Question

I have a table that looks like this:

   Name  |      Temperament
----------------------------------------
 "Husky" | "Smart, Loyal, Cute"
 "Poodle"| "Smart, Cute"
 "Golden"| "Cute, Loyal, Caring, Loving"

And I want to project this data as a group by of the temperaments. For example:

Temperament |             Name            | Count(Optional)
-----------------------------------------------------------   
"Smart"     | "Poodle", "Husky"           | 2
"Loyal"     | "Husky", "Golden"           | 2
"Cute"      | "Poodle", "Golden", "Husky" | 3
"Caring"    | "Golden"                    | 1
"Loving"    | "Golden"                    | 1

My problem is that I couldn't find a way to split the string in my table and manipulate this data. It would be great if anyone can help me with this problem.

If pure SQL can't be done it might be helpful to tell that I'm using Entity Framework and if the solution can be written in it, it might be even better.

Thank you all.

Answer 1

If anyone needs the answer:

var result = (from t in ((from t1 in db.mytables select new {tmp= t1.TEMP1}).Concat(from t2 in db.mytables select new {tmp= t2.TEMP2}).Concat(from t3 in db.mytables select new {tmp= t3.TEMP3})) group t.tmp by t.tmp into g select new { tmp = g.Key, cnx=g.Count()}).ToList();

Hope it'll help someone!