mysql - Group by words in a comma separated string -

In MySQL, the following table is given, where tag is a column that includes separate strings from commas

ID name pay tag ---------------------------- 1 James 5000 sales, marketing How can we get the amount (salary) for each word / tag in John 4000 cells, finance 3 all 3000 hr, marketing, finance

tag? So will the result look like this?

  Tag Total Solaris ------------------------ Sales 9 000 Marketing 8000 Finance 7000 HR 3000  

Any help would be greatly appreciated thanks!

When I recommend generalizing my data structure and do not store a comma-delimited list An easy way to use a "number" table:

  SELECT SUBSTRING_INDEX (SUBSTRING_INDEX (tag, ',', nn), ',', -1) value, amount ( Salary) (select AN + BN * 10 + 1N (Union of all as selection number 1 Union Select All 2 Union Select All 3 Union Select All 4 U Select all 5 Union Select All 6 Union Select All 7 Union Select All 8 Select All of the Association 9) Select One, (Select 0 Union All 1 Union Select All 2 Union All Select 3 Union Select All 4 Union Select All 5 Union Select All 6 Union Select All 7 Union Select All 8 Union Select All 9) B Sr. By n) n WHERE nn & lt; = 1 + (LENGTH (tag) - LENGTH (REPLACE (tag, ',', '')) GROUP by SUBSTRING_INDEX (SUBSTRING_INDEX (tag, ',', nn), ', -1)  

Times that works quite well - I saw it before.