Count string occurences across recordset
I am trying to build a tag cloud from article keywords.
I have an "articles" table with a "keywords" column
1 software, sql, microsoft
2 data, sql, software
3 music, software
What I need is to return something like this:
I guess I have to build a stored procedure with the following commands:
1. loop through the articles
2. split keywords string and add each keyword to a temporary table
3. select count occurrences and group by keyword
Can someone point me to the right direction to build this? I am using SQL Server 2005
Thanks in advance.