table valued function

Question

table valued function

Sarsoura

Ten Centuries

Points: 1220
More actions
November 16, 2012 at 9:44 am

#285103

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

CapnHector SSCoach Points: 16143 More actions · Answer 3

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) 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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

Sarsoura Ten Centuries Points: 1220 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass