Declare @chapters varchar(max)
select @chapters=coalesce(@chapters+',','')+ch.Chapter
from Chapter ch,Subject sb ,Syllabus s,Classroom c
--for syllabus
where s.SyllabusID ='3'
--for classroom
and c.SyllabusID =s.SyllabusID
and c.Classroom =1
--for subject
and sb.SyllabusID =s.SyllabusID
and sb.ClassroomID =c.ClassroomID
--and sb.Subject ='Eng.Lit.'
--for chapters
and ch.SyllabusID =s.SyllabusID
and ch.ClassroomID =c.ClassroomID
and ch.SubjectID =sb.SubjectID
select @chapters
Select * from UF_CSVToTable (@chapters)
Function UF_CSVToTable
USE [CMS2012]
GO
/****** Object: UserDefinedFunction [dbo].[UF_CSVToTable] Script Date: 11/22/2010 17:15:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UF_CSVToTable]
(
@psCSString VARCHAR(max)
)
RETURNS @otTemp TABLE(sID VARCHAR(2000))
AS
BEGIN
DECLARE @sTemp VARCHAR(max)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END
OutPut:
Happy Coding,
Regards,
Krunal Panchal
Your code has a limitation, it accepts only csv format.
ReplyDeletewhat if u have a '/' or '^' separated list.
You can make use split function using a delimiter.
CREATE FUNCTION [dbo].[CNK_CmStrSplit]
(
@SourceString VARCHAR(8000) ,
@Delimiter VARCHAR(10) = ','
)
RETURNS @Values TABLE ( Value VARCHAR(8000) )
WITH SCHEMABINDING
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF LEN(@SourceString) < 1
OR @SourceString IS NULL
RETURN
WHILE @idx != 0
BEGIN
SET @idx = CHARINDEX(@Delimiter, LTRIM(RTRIM(@SourceString)))
IF @idx != 0
SET @slice = LEFT(LTRIM(RTRIM(@SourceString)), @idx - 1)
ELSE
SET @slice = LTRIM(RTRIM(@SourceString))
IF ( LEN(@slice) > 0 )
INSERT INTO @Values
( Value )
VALUES ( @slice )
SET @SourceString = RIGHT(LTRIM(RTRIM(@SourceString)),
LEN(LTRIM(RTRIM(@SourceString)))
- @idx)
IF LEN(LTRIM(RTRIM(@SourceString))) = 0
BREAK
END
RETURN
END