Posted by Erland Sommarskog on 07/13/06 21:47

(adambossy@gmail.com) writes:
> I have a nasty situation in SQL Server 7.0. I have a table, in which
> one column contains a string-delimited list of IDs pointing to another
> table, called "Ratings" (Ratings is small, containing less than ten
> values, but is subject to change.) For example:
>...
> My dilemma is that I can't efficiently read rows in my table, match the
> string of ratingIDs with the values in the Ratings table,

I believe you. That design is a complete disaster, for the very
reasons you mention. It cannot be handled effeciently in a relational
database. The proper design is to put the repeating group in a
sub-table.

> 1) Query my table with the specified criteria, returning ratingIDs as a
> column
> 2) Split the tokens in ratingIDs into a table
> 3) Join this small table with the Ratings table
> 4) Use a CURSOR to iterate through the rows and append it to a string
> 5) Return the string.
>
> My query then returns...
> 1/"Silver, Platinum"
> 2/""
> 3/"Bronze, Silver, Gold"
>
> And is easy to output.

A better alternative, using the current table design, would be get
the select data into a temp table in this way:

INSERT #temp (uniqueid, ratingID)
SELECT uniqueid, ',' + replace(ratingID, ' ', '') + ','
FROM tbl

Note that you add trailing and leading commas, and remove all spaces.
Then loop over Ratings, and say;

UPDATE #temp
SET ratings = replace(ratings, ',' + @rating + ',',
',' + @descr + ',')


But in the long run, a table redesign is what you need.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]