sql - mySQL unique values -

sql - mySQL unique values -

i need unique values table. have single column comma separated keywords. need derive single list of keywords without duplicates. getting count of how each keyword present, too.

from have researched, unpivoting function unknown number of columns?

for example:

keywords

red, blue, yellow blue, orange, black, white brown, black, clear, pink blue, violet, orange

result

color | count

red 1 blue 3 yellow 1 orange 2 black 2 white 1 brown 1 clear 1 pink 1 violet 1

thank in advance!!

** far have tried adding explode_table type procedure, realized can't phone call dynamically view. have been experimenting performing reverse group_concat() on column. haven't been able produce code performs.

my version of echo_me's answer:

select substring_index(substring_index(skeywords, ',', n.n), ',', -1) value , count(*) counts tblpatternmetadata t cross bring together (select a.n + b.n * 10 + 1 n (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a, (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b order n ) n n.n <= 1 + (length(skeywords) - length(replace(skeywords, ',', ''))) grouping value

try that:

select substring_index(substring_index(t.keywords, ',', n.n), ',', -1) value , count(*) counts table1 t cross bring together ( select a.n + b.n * 10 + 1 n (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) ,(select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b order n ) n n.n <= 1 + (length(t.keywords) - length(replace(t.keywords, ',', ''))) grouping value

demo here

mysql sql count unique