Split pipe delimited field

I have fields that look similar to this:

Question Table
----------------------------------------
QuestionId       AnswerChoices
1                      Poor|Below Average|Average|Above Average|Excellent
2                      Low|Medium|High
etc.

Response Table
----------------------------------------
ResponseId       QuestionId         Response
1                         1                        Below Average
1                          2                       Medium
2                          1                       Excellent
2                          2                        High
3                           1                       Poor
3                           2                      Medium
etc.


I am looking for a query which returns the results as follows:
All choices for an answer are returned in the order the answer choice is listed in the pipe delimited field with a count of the number of responses.  So for the above it would look like:

QuestionId         Response          ResponseCount
1                         Poor                   1
1                         Below Average    1
1                         Average               0
1                        Above Average      0
1                        Excellent                1
2                        Low                       0
2                        Medium                  2
2                        High                       1


There can be any number of answer choices in the pipe delimited field.
Ordering in the same order as they appear in the pipe delimited list is important

Thanks.
LVL 35
mrichmonAsked:
Who is Participating?
 
mcmonapConnect With a Mentor Commented:
Hi mrichmon,

You need a function to parse the strings then you are away, you should be able to pretty much copy and paste this as an example:

/*Taken from http://www.nigelrivett.net/SQLTsql/ParseCSVString.html*/
create function dbo.fn_ParseString
(
@String       varchar(8000) ,
@Delimiter      varchar(10)
)
returns @tbl table (s varchar(1000))
as
begin
declare @i int, @j int

      select       @i = 1
      while @i <= len(@String)
      begin
            select      @j = charindex(@Delimiter, @String, @i)
            if @j = 0
            begin
                  select      @j = len(@String) + 1
            end
            insert      @tbl select substring(@String, @i, @j - @i)
            select      @i = @j + len(@Delimiter)
      end
      return
end
GO

DECLARE @Question TABLE (QuestionId INT IDENTITY(1,1), AnswerChoices VARCHAR(255))
DECLARE @QBreakOut TABLE (OrderID INT IDENTITY(1,1), QuestionId INT, Response VARCHAR(255))
DECLARE @Response TABLE (ResponseId INT IDENTITY(1,1), QuestionId INT, Response VARCHAR(255))

INSERT INTO @Question VALUES('Poor|Below Average|Average|Above Average|Excellent')
INSERT INTO @Question VALUES('Low|Medium|High')

INSERT INTO @response (QuestionId, Response) VALUES (1, 'Below Average')
INSERT INTO @response (QuestionId, Response) VALUES (2, 'Medium')
INSERT INTO @response (QuestionId, Response) VALUES (1, 'Excellent')
INSERT INTO @response (QuestionId, Response) VALUES (2, 'High')
INSERT INTO @response (QuestionId, Response) VALUES (1, 'Poor')
INSERT INTO @response (QuestionId, Response) VALUES (2, 'Medium')

DECLARE @q INTEGER
DECLARE c CURSOR FAST_FORWARD
FOR
(SELECT QuestionId FROM @Question)
OPEN c
FETCH NEXT FROM c INTO @q
WHILE @@FETCH_STATUS=0
BEGIN
      INSERT INTO @QBreakOut (QuestionId, Response)
      SELECT @q, * FROM fn_ParseString ((SELECT AnswerChoices FROM @Question WHERE QuestionId = @q),'|')
      FETCH NEXT FROM c INTO @q
END
CLOSE c
DEALLOCATE c

SELECT
      b.QuestionID
      , b.Response
      , COUNT(r.Response) ResponseCount
FROM
      @QBreakOut b
      LEFT JOIN @response r ON b.QuestionId = r.QuestionId AND b.Response = r.Response
GROUP BY
      b.QuestionID
      , b.OrderID
      , b.Response
ORDER BY
      b.OrderID
      , b.QuestionID
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
hi mrichmon

see the genial solution provided in this thread - it's to split a column separated by commas - will work just as well for a pipe or other character.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22546316.html

0
 
mrichmonAuthor Commented:
I ended up starting with that function, but adjusting to account for the ordering so I did not need a cursor, it was just a second column returned by the function return table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.