November 16, 2012 at 9:44 am
I have a table (inherted from some else)
QuestionID,
Layout (Ex.{1,4,3,2}
Answers (ex.'First answer'|'Second answer'|'Third Answer'|'Fourth Answer'}
i.e the answers field has a list of answers separated by | and in sequence
the layout is a list of which order the answers should be displayed on the screen
I would like to have a query that will display the answer seq,answer, answerlayout seq
I have a two functions that will split and answers and split the layout
CREATE FUNCTION [dbo].[fn_Answers_Seq_Split]
(@QuestionID int,
@Answers varchar(4000),
@Delimiter nchar(1)
)
returns @temptable TABLE (QuestionID int,AnswerSeq int identity(1,1),Answer nvarchar(2000) )
as
begin
declare @idx int
declare @slice varchar(4000)
select @idx = 1
if len(@Answers)<1 or @Answers is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@Answers)
if @idx!=0
set @slice = left(@Answers,@idx - 1)
else
set @slice = @Answers
if(len(@slice)>0)
insert into @temptable(QuestionID,Answer) values(@QuestionID,@slice)
set @Answers = right(@Answers,len(@Answers) - @idx)
if len(@Answers) = 0 break
end
return
end
and used the following query to display the list of answer seq, answer , question but was not able to include the layout
select MC.QuestionID,
LS.AnswerSeq,
LS.Answer
from Question MC
cross apply dbo.fn_Answers_Seq_Split(MC.QuestionID,MC.Answers,'|') as LS
Thanks
November 16, 2012 at 9:46 am
Two points.
There are far more efficient split functions around than one using a loop, multi-statement table-valued functions can have rather unpleasant performance implications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2012 at 1:36 pm
As previously stated the type of splitter you have is very slow and if at all possible replace that denormalized table with something easier to work with. You can parse this stuff out. The following works with your sample data but I suspect the real scenario will be more complicated.
Notice how I posted some ddl and sample data to get started?
;with Data (QuestionID, Layout, Answers) as
(
select 1, '1,4,3,2', 'First Answer|Second Answer|Third Answer|Fourth Answer'
)
, Layout as
(
select Data.*, l.Item as LayoutSplit, l.ItemNumber as LayoutNumber
from Data
outer apply dbo.DelimitedSplit8K(Layout, ',') l
)
, Answers as
(
select Data.*, a.Item as Answer, a.ItemNumber as AnswerNumber
from Data
outer apply dbo.DelimitedSplit8K(Answers, '|') a
)
select layout.QuestionID, Layout.Layout, LayoutSplit, Answers.Answers, Answer
from layout
join Answers on layout.QuestionID = Answers.QuestionID and layout.LayoutNumber = Answers.AnswerNumber
You can find the code the DelimitedSplit8K function by following the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2012 at 1:38 pm
Here is some sample data so i could chew on it. i would suggest looking at DelimitedSplit8k for this, the link is in my signature or you can find it by searching this site for Jeff Moden's DelimitedSplit8k
;WITH cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'))a(N)
CROSS JOIN (VALUES ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'), ('#'))b(N)
),
SampleData AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS QuestionID,
SUBSTRING(Alphabet,ABS(CHECKSUM(NEWID())) % 15 + 1,ABS(CHECKSUM(NEWID())) % 15 + 1) + '|' +
SUBSTRING(Alphabet,ABS(CHECKSUM(NEWID())) % 15 + 1,ABS(CHECKSUM(NEWID())) % 15 + 1) + '|' +
SUBSTRING(Alphabet,ABS(CHECKSUM(NEWID())) % 15 + 1,ABS(CHECKSUM(NEWID())) % 15 + 1) + '|' +
SUBSTRING(Alphabet,ABS(CHECKSUM(NEWID())) % 15 + 1,ABS(CHECKSUM(NEWID())) % 15 + 1) AS QuestionString
FROM cteTally
CROSS JOIN (VALUES ('abcdefghijklmnopqrstuvwxyz'))X(Alphabet)
)
SELECT QuestionID, ItemNumber AS AnswerNumber, Item AS Answer
FROM SampleData
CROSS APPLY udf_DelimitedSplit8K(QuestionString,'|')
ORDER BY QuestionID
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 16, 2012 at 3:52 pm
Thanks to all
Can you provide me with a more efficient splitter thanks I am going to try to convience normalizing.
You are right
Thanks
November 17, 2012 at 4:39 am
Sarsoura (11/16/2012)
Can you provide me with a more efficient splitter
capnhector (11/16/2012)
i would suggest looking at DelimitedSplit8k for this, the link is in my signature or you can find it by searching this site for Jeff Moden's DelimitedSplit8k
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply