sql - 将值拆分为多行

Question

这个问题已经有答案了:

已关闭 10 年前。

Possible Duplicate:
Turning a Comma Separated string into individual rows

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

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

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

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，只需要一些有关如何拆分主题字段的帮助。

谢谢

Answer 1

您将需要使用某种与此类似的表值分割函数:

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 与您的表连接:

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

如果您想在不使用 split 函数的情况下执行此操作，则可以使用 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