Split Comma-Delimited Column into Multiple Rows

We had post about combining multiple rows into a column in the past. Sometimes, on the contrary, we would want to split character-delimited column into multiple rows. In this post, we will see try to split comma-delimited column into multiple rows. Sample Data:

End result after split into rows:

Query:

-- Create temporary table to store UserGroup
create table #UserGroup (GroupID int, UserGroup nvarchar(1000), Users nvarchar(1000))
insert into #UserGroup values (1, 'Group 1', 'Himura,Selvi,Superman')
INSERT INTO #UserGroup VALUES ( 2, 'Group 2', 'Luck,Smarty')
INSERT INTO #UserGroup VALUES ( 3, 'Group 3', 'Lucky')

--Display the sample data
select * from #UserGroup

--Split comma-delimited column into multiple rows
;WITH tmp(GroupID, UserGroup, Username, Users) AS
(
	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM #UserGroup
	where Users is not null
	UNION all

	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM tmp
	WHERE
		Users > ''
)
select GroupID, UserGroup, Username
from tmp
order by GroupID, UserGroup

Read More