SEARCH:
NAVIGATION:
This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.
| N U T S H E L L | |
| Module | list2table |
|---|---|
| Description | Converts a list to a table |
| Environment | SQL Server 2000/2005 |
| Author | Michael Sorens |
| Since | CleanCode 0.9.17 |
This function converts a list, passed as a single string, into a table. The string enumerates all your list elements separated by a single character of your choice. In contrast with list2tableIndexed, list2table returns a one-column table. This function is appropriate if you do not need array indexing of your list in the returned table. However, you could still manipulate it that way after the fact, if needed, by using the IDENTITY attribute on your local table variable, e.g.:
DECLARE @myList TABLE ( id int IDENTITY(1,1), name VARCHAR(10) )
INSERT INTO @myList
SELECT * FROM [list2table]('item1/item2/item3', '/')
select name from @myList where id=2
Note that the presence of a separator character always indicates an element on both sides. Specifically, if you use a comma as a delimiter, then "A,,B,C" returns a table with 4 rows, not 3. The second row value is an empty string. Similarly, "A,B,C," and ",A,B,C" and "A,,B,C" also return tables with 4 rows, with empty strings inserted where no data is provided.
list2table(@list, @delimiter)
where:
@list ::= "'" { [item] [delimiter] } [item] "'"
@delimiter ::= [char]
select * from list2table('item1,item2,item3', ',')
select * from list2table('item1#item2##item4#', '#')
Adapted from Use Table-Valued Functions as Arrays in SQL Server.
See also: list2tableIndexed
