DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

How To Convert Comma Seprated (CSV) List Into A Single Column Multiple Rows

12.19.2011
| 2579 views |
  • submit to reddit
        // description of your code here

declare	@list nvarchar(max) = 'America, India, Pakistan, Iran, Irak, Australia';
declare	@Delimiter nvarchar(10) = ',';
DECLARE @LEN INT
SET @LEN=LEN(@List)+1
 
;With UserList AS
( 
	SELECT	
			cast(isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN) as int) AS nEnd,
			RTRIM(LTRIM(SUBSTRING(@List,1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN)-1))) AS Users
	UNION All
	SELECT	
			cast(isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN) as int),
			RTRIM(LTRIM(SUBSTRING(@List,nEnd+1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN)-nEnd-1)))
	FROM UserList
	WHERE nEnd<@LEN
)
SELECT NULLIF(Users,'') AS Users FROM UserList where 
--OUTPUT
America
India
Pakistan
Iran
Irak
Australia

<a href="http://www.java-forums.org/java-software/"><strong>Java Software</strong></a>