String Roll to Tabular Roll

01, 02, 03, 04, 05, 06, 07, 08, 09, 10
01
02
03
04
05
06
07
08
09
10

USE [dbAsaTest]
GO
/****** Object: UserDefinedFunction [dbo].[fn_Exam_Absent_Student] Script Date: 07/02/2012 17:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_Exam_Absent_Student]
(
@TermId VARCHAR(50),
@CourseId VARCHAR(50),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data VARCHAR(256)
)
------ select * from dbo.fn_Exam_Absent_Student('CSE2012FEB_2K11Y1T1','CSE2006MAYCSE1101',',')
BEGIN
DECLARE @string VARCHAR(MAX), @start INT, @end INT
SELECT @string=AbsStudentRoll FROM T_Absent_List WHERE TermId=@TermId AND CourseId=@CourseId
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1
BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s