T-SQL: Multiple Separator Parsing

Question

I need to be able to query a SharePoint database for survey results. The type of data I encountered is the value of the "Rating Scale". Thus, the data in each column of the table represents a whole group of subqueries and their responses.

So, here is an example of what is in the ONE column:

1. Our function determined how availability is measured by hardware / software in Production; # 3 # 2. Availability threshold levels exist for our function (for example, SLA); # 3 # 3. Our function corresponds to a certain process with threshold violations: # 4 # 4. Our function collects and maintains accessibility data, # 4 # 5. A comparative analysis helps to determine the trend with accessibility data, # 4 # 6. Agreements working level (OLA) defines our interaction with other internal teams, # 4 #

Questions end with a semicolon and their answers are inside two # signs. So, the answer to the first question is 3.

When I export the survey results, it formats each question as a column heading and the answer as a value in the cell below, which is ideal for getting the average value for each question, and he would like to be able to replicate this from the SQL query.

But if I could get the query results in two columns (question, answer) ... I would be delighted with this.

Any help is appreciated.

Many thanks

Hank stolings

***** ADDITION: **

This was my version of the solution for listeners ... THANKS again!

DECLARE @Table TABLE( QuestionSource VARCHAR(50), QA VARCHAR(5000) ) DECLARE @ReturnTable TABLE( QuestionSource VARCHAR(50), Question VARCHAR(5000), Answer int ) DECLARE @XmlField XML, @QuestionSource VARCHAR(50) INSERT INTO @Table SELECT 'Availability' AS QuestionSource,CONVERT(varchar(5000),ntext1) FROM UserData WHERE tp_ContentType = 'My Survey' INSERT INTO @Table SELECT 'Capacity' AS QuestionSource,CONVERT(varchar(5000),ntext2) FROM UserData WHERE tp_ContentType = 'My Survey' --SELECT * FROM @Table DECLARE Cur CURSOR FOR SELECT QuestionSource, CAST(Val AS XML) XmlVal FROM ( SELECT QuestionSource, LEFT(Vals, LEN(Vals) - LEN('<option><q>')) Val FROM ( SELECT QuestionSource, '<option><q>' + REPLACE(REPLACE(REPLACE(QA,'&','&amp;'), ';#','</q><a>'), '#', '</a></option><option><q>') Vals FROM @Table ) sub ) sub OPEN Cur FETCH NEXT FROM Cur INTO @QuestionSource,@XmlField WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @ReturnTable SELECT @QuestionSource, T.split.query('q').value('.', 'nvarchar(max)') question, T.split.query('a').value('.', 'nvarchar(max)') answer FROM @XmlField.nodes('/option') T(split) FETCH NEXT FROM Cur INTO @QuestionSource,@XmlField END CLOSE Cur DEALLOCATE Cur SELECT * FROM @ReturnTable 

Answer 1

OK, we will see. I had to use a cursor, as this would probably be better achieved in a programming language such as C #, but here it goes ... Using Sql Server 2005, try the following. Let me know if you need an explanation.

 DECLARE @Table TABLE( QuestionSource VARCHAR(50), QA VARCHAR(1000) ) DECLARE @ReturnTable TABLE( QuestionSource VARCHAR(50), Question VARCHAR(1000), Answer VARCHAR(10) ) DECLARE @XmlField XML, @QuestionSource VARCHAR(40) INSERT INTO @Table SELECT 'Availability','1. Our function has defined how Availability is measured the hardware/software in Production;#3#2. Availability threshold levels exist for our function (eg, SLA''s);#3#3. Our function follows a defined process when there are threshold breaches;#4#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#4#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#4#' INSERT INTO @Table SELECT 'Capacity', '1. Our function has defined how Availability is measured the hardware/software in Production;#1#2. Availability threshold levels exist for our function (eg, SLA''s);#2#3. Our function follows a defined process when there are threshold breaches;#3#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#5#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#6#' DECLARE Cur CURSOR FOR SELECT QuestionSource, CAST(Val AS XML) XmlVal FROM ( SELECT QuestionSource, LEFT(Vals, LEN(Vals) - LEN('<option><q>')) Val FROM ( SELECT QuestionSource, '<option><q>' + REPLACE(REPLACE(QA, ';#','</q><a>'), '#', '</a></option><option><q>') Vals FROM @Table ) sub ) sub OPEN Cur FETCH NEXT FROM Cur INTO @QuestionSource, @XmlField WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @ReturnTable SELECT @QuestionSource, T.split.query('q').value('.', 'nvarchar(max)') question, T.split.query('a').value('.', 'nvarchar(max)') answer FROM @XmlField.nodes('/option') T(split) FETCH NEXT FROM Cur INTO @QuestionSource, @XmlField END CLOSE Cur DEALLOCATE Cur SELECT * FROM @ReturnTable 

Answer 2

You should have the split function installed, but as soon as you get it, try this free cursor solution:

I prefer a number table approach to split a string in TSQL

For this method to work, you need to complete this setup at once:

 SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) 

Once the Numbers table is configured, create this split function that will return empty rows and line numbers:

 CREATE FUNCTION [dbo].[FN_ListToTableRows] ( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000)--REQUIRED, the list to split apart ) RETURNS TABLE AS RETURN ( ---------------- --SINGLE QUERY-- --this WILL return empty rows ---------------- SELECT ROW_NUMBER() OVER(ORDER BY number) AS RowNumber ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue FROM ( SELECT @SplitOn + @List + @SplitOn AS ListValue ) AS InnerQuery INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue) WHERE SUBSTRING(ListValue, number, 1) = @SplitOn ); GO 

Now you can easily split the CSV row into a table and join it, NOTE. This split function returns empty lines and line numbers:

 select * from dbo.FN_ListToTableRows(',','1,2,3,,,4,5,6777,,,') 

CONCLUSION:

 RowNumber ListValue -------------------- ------------ 1 1 2 2 3 3 4 5 6 4 7 5 8 6777 9 10 11 (11 row(s) affected) 

Now you can use CROSS APPLY to split each row in the table, for example:

 DECLARE @YourTable table (RowID int, RowValue varchar(8000)) INSERT INTO @YourTable VALUES (1,'1. Our function has defined how Availability is measured the hardware/software in Production;#3#2. Availability threshold levels exist for our function (eg, SLA''s);#3#3. Our function follows a defined process when there are threshold breaches;#4#4. Our function collects and maintains Availability data;#4#5. Comparative analysis helps identify trending with the Availability data;#4#6. Operating Level Agreements (OLA''s) guide our interaction with other internal teams;#4#') INSERT INTO @YourTable VALUES (2,'1. one;#1#2. two;#2#3. three;#3#') INSERT INTO @YourTable VALUES (3,'1. aaa;#1#2. bbb;#2#3. ccc;#3#') ;WITH AllRows As ( SELECT o.RowID,st.RowNumber,st.ListValue AS RowValue FROM @YourTable o CROSS APPLY dbo.FN_ListToTableRows('#',LEFT(o.RowValue,LEN(o.RowValue)-1)) AS st ) SELECT a.RowID,a.RowValue AS Question, b.RowValue AS Answer FROM AllRows a LEFT OUTER JOIN AllRows b ON a.RowID=b.RowID AND a.RowNumber+1=b.RowNumber WHERE a.RowNumber % 2 = 1 

OUTPUT:

 RowID Question Answer ----------- ----------------------------------------------------------------------------------------------- ------- 1 1. Our function has defined how Availability is measured the hardware/software in Production; 3 1 2. Availability threshold levels exist for our function (eg, SLA's); 3 1 3. Our function follows a defined process when there are threshold breaches; 4 1 4. Our function collects and maintains Availability data; 4 1 5. Comparative analysis helps identify trending with the Availability data; 4 1 6. Operating Level Agreements (OLA's) guide our interaction with other internal teams; 4 2 1. one; 1 2 2. two; 2 2 3. three; 3 3 1. aaa; 1 3 2. bbb; 2 3 3. ccc; 3 (12 row(s) affected)