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
