[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2285
  • Last Modified:

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.
0
mrichmon
Asked:
mrichmon
1 Solution
 
mcmonapCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now