Convert CSV list into a single column multiple rows





5.00/5 (2 votes)
declare @list nvarchar(max) = 'Imdad,Kuber,Maninder,Nirmal,Ajit';
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
--Users
---------
--Imdad
--Kuber
--Maninder
--Nirmal
--Ajit
Thanks,
Imdadhusen