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?
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)
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);
STRING_SPLIT and not dbo.split. learn.microsoft.com/en-us/sql/t-sql/functions/…dbo.split is usually a user defined function, in most of the implementations. Just putting my 2-cents that in 2016, there is a system function that does what we have had to implement.I know this is an older post but I wanted to add my solution so that I can find it in the future. I had to make a slight tweak to Stephan's Solution to account for values that do NOT contain a delimiter:
DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));
INSERT INTO @yourTable
VALUES (1,'a,b,c,d'),
(2,'e'),
(3,'f'),
(4,'g,h,i');
WITH CTE
AS
(
SELECT ID,
[xml_val] = CAST('<t>' +
CASE WHEN CHARINDEX(',', SomeValue) > 0
THEN REPLACE(SomeValue,',','</t><t>')
ELSE SomeValue
END + '</t>' AS XML)
FROM @yourTable
)
SELECT ID,
[SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)
