Split comma separated string table row into separate rows using TSQL
17,146
Solution 1
Alternatively, you could use XML like so:
DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));
INSERT INTO @yourTable
VALUES (1,'a,b,c,d'),
(2,'e,f,g');
WITH CTE
AS
(
SELECT ID,
[xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
FROM @yourTable
)
SELECT ID,
[SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
Solution 2
You use cross apply
. Something like this:
select t.id, s.val as SomeValue
from table t cross apply
dbo.split(SomeValue, ',') as s(val);
Author by
Stewart Alan
Updated on July 13, 2022Comments
-
Stewart Alan almost 2 years
Say I have a query that returns the following
ID SomeValue 1 a,b,c,d 2 e,f,g
Id like to return this as follows:
ID SomeValue 1 a 1 b 1 c 1 d 2 e 2 f 2 g
I already have a UDF calls Split that will accept a string and a delimter and return it as a table with a single column called [Value]. Given this, How shoudl the SQL look to achieve this?