Here is quick SQL function to convert list to table.
-- sql function to convert list to table
CREATE FUNCTION fn_listtotable
(
@list nvarchar(max),
@delimeter nvarchar(100)
)
RETURNS @split TABLE
(
sno int identity,
value varchar(max)
)
BEGIN
Declare @xml XML
select @xml = cast('<A>'+ replace(@list,@delimeter,
'</A><A>')+ '</A>' as xml)
INSERT INTO @split
select t.value('.','varchar') as inVal
from @xml.nodes('/A') as x(t)
RETURN
END
The above query exploits the Xquery capabilities of SQL Server to split the delimited values. The function can be used as shown below. There are other methods too however I found this quick and easy.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
This only works if the list items are onyl single character strings like A,B,C etc
It failes when the list items are multiple characters e.g. ABC,DEF,GHI
In the later case, the returned table has only the 1st character of the list items
You need to give it a bigger dimension like varchar(3).