Wednesday, November 4, 2020

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
Share:

0 comments:

Post a Comment

You may be intersted in

Related Posts

Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone. There...

About Me

My photo
Is an ordinary man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing.

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Leave a Message