很晚了，但是试试这个:

SELECT ColumnID, Column1, value  --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample  
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values

所以我们有了这个: tbl_Sample:

ColumnID|   Column1 |   Tags
--------|-----------|-------------
1       |   ABC     |   10,11,12    
2       |   PQR     |   20,21,22

运行此查询后:

ColumnID|   Column1 |   value
--------|-----------|-----------
1       |   ABC     |   10
1       |   ABC     |   11
1       |   ABC     |   12
2       |   PQR     |   20
2       |   PQR     |   21
2       |   PQR     |   22

谢谢!

检查这个

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

截至2016年2月-见talltytable示例-很可能从2014年2月开始超过我的TVF。为子孙后代保留以下原始帖子:

上面的例子中重复的代码太多了。我不喜欢cte和XML的性能。还有一个显式Id，以便特定于订单的消费者可以指定order BY子句。

CREATE FUNCTION dbo.Split
(
    @Line nvarchar(MAX),
    @SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Data nvarchar(100) NOT NULL
)
AS
BEGIN
    IF @Line IS NULL RETURN;

    DECLARE @split_on_len INT = LEN(@SplitOn);
    DECLARE @start_at INT = 1;
    DECLARE @end_at INT;
    DECLARE @data_len INT;

    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at);
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END;
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len;
    END;

    RETURN;
END;

DECLARE @id_list VARCHAR(MAX) = '1234,23,56,576,1231,567,122,87876,57553,1216';
DECLARE @table TABLE ( id VARCHAR(50) );
DECLARE @x INT = 0;
DECLARE @firstcomma INT = 0;
DECLARE @nextcomma INT = 0;

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1; -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END;
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) );
        SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1);
        SET @x = @x - 1;
    END;

SELECT  *
FROM    @table;

以下工作在sql server 2008

select *, ROW_NUMBER() OVER(order by items) as row# 
from 
( select 134 myColumn1, 34 myColumn2, 'd,c,k,e,f,g,h,a' comaSeperatedColumn) myTable
    cross apply 
SPLIT (rtrim(comaSeperatedColumn), ',') splitedTable -- gives 'items'  column 

将得到所有的笛卡尔积与原表列加上“项目”的分割表。

我知道它有很多答案，但我想写我的版本的分裂函数像其他人和像string_split SQL Server 2016本机函数。

create function [dbo].[Split]
(
    @Value nvarchar(max),
    @Delimiter nvarchar(50)
)
returns @tbl table
(
    Seq int primary key identity(1, 1),
    Value nvarchar(max)
)
as begin
    declare @Xml xml = cast('<d>' + replace(@Value, @Delimiter, '</d><d>') + '</d>' as xml);

    insert into @tbl
            (Value)
    select  a.split.value('.', 'nvarchar(max)') as Value
    from    @Xml.nodes('/d') a(split);
    
    return;
end;

Seq列是支持快速连接其他实表或Split函数返回表的主键。 使用XML函数支持大数据(当你有大数据时，循环版本会显著变慢)

这是问题的答案。

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';


select  t.SomeID, t.OtherID, s.Value
from    Testdata t
        cross apply dbo.Split(t.String, ',') s;

--Output
SomeID  OtherID Value
1       9       18
1       9       20
1       9       22
2       8       17
2       8       19
3       7       13
3       7       19
3       7       20
4       6       
9       11      1
9       11      2
9       11      3
9       11      4

加入Split与其他Split

declare @Names nvarchar(max) = 'a,b,c,d';
declare @Codes nvarchar(max) = '10,20,30,40';

select  n.Seq, n.Value Name, c.Value Code
from    dbo.Split(@Names, ',') n
        inner join dbo.Split(@Codes, ',') c on n.Seq = c.Seq;

--Output
Seq Name    Code
1   a       10
2   b       20
3   c       30
4   d       40

分开两次

declare @NationLocSex nvarchar(max) = 'Korea,Seoul,1;Vietnam,Kiengiang,0;China,Xian,0';

with rows as
(
    select  Value
    from    dbo.Split(@NationLocSex, ';')
)
select  rw.Value r, cl.Value c
from    rows rw
        cross apply dbo.Split(rw.Value, ',') cl;

--Output
r                       c
Korea,Seoul,1           Korea
Korea,Seoul,1           Seoul
Korea,Seoul,1           1
Vietnam,Kiengiang,0     Vietnam
Vietnam,Kiengiang,0     Kiengiang
Vietnam,Kiengiang,0     0
China,Xian,0            China
China,Xian,0            Xian
China,Xian,0            0

分割成列

declare @Numbers nvarchar(50) = 'First,Second,Third';

with t as
(
    select  case when Seq = 1 then Value end f1,
            case when Seq = 2 then Value end f2,
            case when Seq = 3 then Value end f3
    from    dbo.Split(@Numbers, ',')
)
select  min(f1) f1, min(f2) f2, min(f3) f3
from    t;

--Output
f1      f2      f3
First   Second  Third

按范围生成行

declare @Ranges nvarchar(50) = '1-2,4-6';

declare @Numbers table (Num int);
insert into @Numbers values (1),(2),(3),(4),(5),(6),(7),(8);

with t as
(
    select  r.Seq, r.Value,
            min(case when ft.Seq = 1 then ft.Value end) ValueFrom,
            min(case when ft.Seq = 2 then ft.Value end) ValueTo
    from    dbo.Split(@Ranges, ',') r
            cross apply dbo.Split(r.Value, '-') ft
    group by r.Seq, r.Value
)
select  t.Seq, t.Value, t.ValueFrom, t.ValueTo, n.Num
from    t
        inner join @Numbers n on n.Num between t.ValueFrom and t.ValueTo;

--Output
Seq Value   ValueFrom   ValueTo Num
1   1-2     1           2       1
1   1-2     1           2       2
2   4-6     4           6       4
2   4-6     4           6       5
2   4-6     4           6       6