Split a comma separated column into rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Split a comma separated column into rows

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2014-03-23 : 18:58:04

I have a table that looks like this:

ID | Categories
1 | ,4,5,6
2 | ,24,62,64
3 | ,41,5,64

I'm trying to write a query that would output this as:

ID | Category
1 | 4
1 | 5
1 | 6
2 | 24
2 | 62
2 | 64
3 | 41
3 | 5
3 | 64

Is this possible?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-24 : 03:03:05

[code]

;WITH aCTE
AS
(select 1 as ID,',4,5,6' as Categories Union all
select 2 , ',24,62,64' union all
select 3 , ',41,5,64')

,cteXML
AS
(
select
ID
,CAST('<Categories><categ>' + replace(right(Categories,len(Categories)-1),',','</categ><categ>')+'</categ></Categories>' as XML) as Categories
from aCTE
)

select
ID

,t.u.value('.','varchar(50)') as Category
from cteXML
cross apply
Categories.nodes('Categories/categ') t(u)
[/code]

output

[code]
ID Category
1 4
1 5
1 6
2 24
2 62
2 64
3 41
3 5
3 64
[/code]

S

sabinWeb MCP

alexjamesbrown
Starting Member

48 Posts

Posted - 2014-03-26 : 20:18:17

thanks...

how can i replace the hardcoded values with my table?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-28 : 01:51:46

[code]
select 1 as ID,',4,5,6' as Categories Union all
select 2 , ',24,62,64' union all
select 3 , ',41,5,64'
[/code]

will be replace by your select like this:
[code]
SELECT ID, Categories
FROM yourTable
[/code]

sabinWeb MCP

alexjamesbrown
Starting Member

48 Posts

Posted - 2014-04-01 : 03:57:56

I get

Invalid length parameter passed to the RIGHT function.

I guess one of my "products" has no categories?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-01 : 04:02:09

SELECT ID, Categories
FROM yourTable

WHERE Categories IS NOT NULL

sabinWeb MCP

alexjamesbrown
Starting Member

48 Posts

Posted - 2014-04-01 : 16:49:55

Perfect, thanks