Split comma separated string table row into separa

问题:

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?

回答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)

回答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);

回答3:

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)