sql - 将值拆分为多行

这个问题已经在这里有了答案:




已关闭8年。






我从存储过程中获得以下输出，并且想知道将值拆分为多行的最佳方法。

reference   name                            subjects       subjectstitle
LL9X81MT    Making and Decorating Pottery   F06,F27,F38       NULL

我需要修剪逗号处的subject字段，并将信息复制到三行中，以便数据如下。

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

我正在使用MS SQL Server 2008设置这些SP，仅需要一些有关如何拆分主题字段的帮助。

谢谢，

最佳答案

您将要使用某种类似于以下内容的表值拆分函数:

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;

然后，您可以使用outer apply与yourtable联接:

select t1.reference,
  t1.name,
  t1.subjectstitle,
  i.items subjects
from yourtable t1
outer apply dbo.split(t1.subjects, ',') i

给出这样的结果:

| 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 |

参见SQL fiddle with Demo

如果要在没有拆分功能的情况下执行此操作，则可以使用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

参见SQL Fiddle with Demo

关于sql - 将值拆分为多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13159526/