# split string for calculations

I'm doing some basic statistical calculations on the results of a survey. A survey consists of several sections and each section has some questions in it. The answers to each question in each section are stored.

The `ResultsTable` table has `Result` column which contains computed result for the entire survey as well as overall results for each section, separated by double pipe. For example,

`Moderate||Low||High||Low||Low||Low||NA||NA`

Above, Moderate is overall survey result, Low is result for section 1, High is for section 2 and so on.

(Then I would be computing some statistics, such as % of people who have High as their result for a specific section. Or, of those who have High for a specific section the % who answered Yes or No to a specific question in that section, etc.)

The problem I'm facing is that I need to split string, which I think could cause performance problems. What I have right now is as follows.

``````SELECT COUNT(*) [Personal_Health_High_Risk] FROM
(
SELECT rt.*
FROM ResultsTable rt
WHERE
rt.CampaignID IN (SELECT Value FROM ufn_ParseDelimitedString(@campaignIDs, ',')) AND
rt.SectionID = @sectionID
) sq
WHERE
-- where 3rd section result is High
(SELECT Value FROM ufn_ParseDelimitedString(sq.Result, '||') WHERE RowNumber = 4 ) LIKE '%High%'
``````

The `ufn_ParseDelimitedString` function splits a string using given delimiter and returns resulting tokens in a table. For example,

``````-- ufn_ParseDelimitedString output:
--  [RowNumber] [Value]
--  1           Moderate
--  2           Low
--  3           High
--  ...
``````

So, is it better to continue with what I'm doing - basically split the string on each select? OR I was thinking taking that inner `SELECT` in code above and saving output into temporary table and actually expanding the concatenated result string into separate columns. This way I could reuse temporary table for various other calculations. What are your thoughts on this?