Count string occurences across recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count string occurences across recordset

Author

Topic

goldstein
Starting Member

2 Posts

Posted - 2007-07-09 : 11:36:48

Hello there,
I am trying to build a tag cloud from article keywords.
I have an "articles" table with a "keywords" column

id keywords
================
1 software, sql, microsoft
2 data, sql, software
3 music, software

What I need is to return something like this:

keyword occurences
========================
software 3
sql 2
microsoft 1
data 1
music 1

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 11:52:58

quote:
I am using SQL Server 2005

Then please post in a 2005 forum. Not 2000.

DECLARE @TABLE TABLE
(
	id 		int,
	keywords	varchar(100)
)
INSERT INTO @TABLE
SELECT	1, 'software, sql, microsoft'	UNION ALL
SELECT	2, 'data, sql, software'	UNION ALL
SELECT	3, 'music, software'

-- fnParseList IS FROM http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
SELECT	keyword = Data, occurences = COUNT(*)
FROM	@TABLE t CROSS apply fnParseList(',', keywords)
GROUP BY Data

KH
[spoiler]Time is always against us[/spoiler]

goldstein
Starting Member

2 Posts

Posted - 2007-07-09 : 12:13:27

thanks a lot khtan
works fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources