Here is my issue:
I have a column with the following data in an sql column:
Answers
=======
1:2:5: <--- notice my delimiter
I need to be able to break up the digits into a result set that i can join against a lookup table such as
Answers_Expanded
=======
1 apple
2 pear
3 cherry
4 mango
5 grape
and return
Answers
=======
apple pear grape
Any such way?
Thanks!
This is a bit of a hack (the LIKE, the XML PATH, and the STUFF), and it assumes that you want the answers ordered by their ID as opposed to matching up with the original order in the multivalued column...
But this gives the results you're looking for:
SELECT STUFF((
SELECT ' ' + ae.Answer
FROM
Answers_Expanded ae
JOIN Answers a ON ':' + a.Answers LIKE '%:' + CAST(ae.ID AS VARCHAR) + ':%'
ORDER BY ae.ID
FOR XML PATH(''))
, 1, 1, '') AS Answers
This works because:
LIKE finds any Answer_Expanded rows that match the multivalued column.XML PATH simulates a group concatenation... and allows for ' ' to be specified as the delimiterSTUFF removes the leading delimiter.
This blog post has a good example of a user defined function that will return a table with the values from your delimited string in a column. You can then join that table to your Answers_Expanded table to get your value.
This works fine if you are parsing reasonably short strings, and if you are doing it as a one time thing, but if you have a table with your answers stored in a column like that, you don't want to be running this on the whole table as it will be a large performance hit. Ideally you'd want to avoid getting delimited strings like this in SQL.
i would suggest that you save your answers in a way that one cell has only one number...not multible information in one cell. (violation of the 1st normal form).
otherwise you better use some higher sql language such as T-SQL.
