Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes ha
s multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would retur
n a recordset with each errorCode as its own field. If the newly generated f
ield values could have the same name as the errorCode, that would be good as
well.
Thanks,
StewI suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew|||Aaron, I understand your suggestion and gave it some consideration. However,
I'm unaware of all the possible error codes which may be encountered. Meani
ng I'd be entering new errorcodes as they are encountered - which I'm not in
terested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. Ho
wever if there is a query that would satisfy my original request, I'd still
be interested in that.
Thanks,
Stew|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net