I have a column in a table with words separated by comma. I need to count each occurence of each word
My column looks like : ('a, b, c'), ('a, b, d'), ('b, c, d'), ('a'), ('a, c');
(fiddle at the bottom)
Here is what I get :
MyCol Count
-----------------
a 1
a, b, c 3
a, b, d 3
a, c 2
b, c, d 3
But here is what I expect
MyCol Count
-------------
a 4
b 3
c 3
d 2
Here is what I've done so far :
select MyCol, COUNT(*)
from Test
cross apply string_split(MyCol, ',')
group by MyCol
Fiddle : http://sqlfiddle.com/#!18/4e52e/3
Please note the words are separated by a comma AND a space
You are using the wrong column. Simply use the [value]
column (returned from the STRING_SPLIT()
call) and remove the space characters (using TRIM()
for SQL Server 2017+ or LTRIM()
and RTRIM()
for earlier versions):
SELECT TRIM(s.[value]) AS [value], COUNT(*) AS [count]
FROM Test t
CROSS APPLY STRING_SPLIT(t.MyCol, ',') s
GROUP BY TRIM(s.[value])
ORDER BY TRIM(s.[value])