SQL 在多行上拆分值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13159526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split values over multiple rows
提问by PipBoy
Possible Duplicate:
Turning a Comma Separated string into individual rows
可能的重复：
将逗号分隔的字符串转换为单独的行
I have the following output from a stored procedure and was wondering the best way to split the values into multiple rows.
我从存储过程获得以下输出，并且想知道将值拆分为多行的最佳方法。
reference name subjects subjectstitle
LL9X81MT Making and Decorating Pottery F06,F27,F38 NULL
I need to trim the subjects field at the comma's and duplicate the information over three rows so the data would be as follows.
我需要修剪逗号处的主题字段并将信息复制到三行中，以便数据如下。
reference name subjects subjectstitle
LL9X81MT Making and Decorating Pottery F06 NULL
LL9X81MT Making and Decorating Pottery F27 NULL
LL9X81MT Making and Decorating Pottery F38 NULL
I'm using MS SQL Server 2008 to setup these SP's and just need some help on how to split the subjects field up.
我正在使用 MS SQL Server 2008 来设置这些 SP，只需要一些关于如何拆分主题字段的帮助。
Thanks,
谢谢，
回答by Taryn
You will want to use some sort of table-valued split function similar to this:
您将需要使用某种类似于此的表值拆分函数：
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
You can then use outer applyto join with yourtable:
然后你可以使用outer apply加入你的表：
select t1.reference,
t1.name,
t1.subjectstitle,
i.items subjects
from yourtable t1
outer apply dbo.split(t1.subjects, ',') i
Giving a result like this:
给出这样的结果：
| REFERENCE | NAME | SUBJECTSTITLE | SUBJECTS |
------------------------------------------------------------------------
| LL9X81MT | Making and Decorating Pottery | (null) | F06 |
| LL9X81MT | Making and Decorating Pottery | (null) | F27 |
| LL9X81MT | Making and Decorating Pottery | (null) | F38 |
If you want to do this without a split function, then you can use CTE:
如果您想在没有拆分功能的情况下执行此操作，则可以使用 CTE：
;with cte (reference, name, subjectstitle, subjectitem, subjects) as
(
select reference,
name,
subjectstitle,
cast(left(subjects, charindex(',',subjects+',')-1) as varchar(50)) subjectitem,
stuff(subjects, 1, charindex(',',subjects+','), '') subjects
from yourtable
union all
select reference,
name,
subjectstitle,
cast(left(subjects, charindex(',',subjects+',')-1) as varchar(50)) ,
stuff(subjects, 1, charindex(',',subjects+','), '') subjects
from cte
where subjects > ''
)
select reference, name, subjectstitle, subjectitem
from cte
回答by Kaf
This will do without a split function
这将不需要拆分功能
SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitile
FROM
(
SELECT *,
CAST('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml
FROM [yourTable] T
) T1
CROSS APPLY
(
SELECT my_Data.D.value('.','varchar(50)') as my_Splits
FROM T1.my_Xml.nodes('X') as my_Data(D)
) T2
