在多行上拆分值 [英] Split values over multiple 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

我正在使用 MS SQL Server 2008 来设置这些 SP，只是需要一些关于如何拆分主题字段的帮助.

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.

谢谢，

推荐答案

您可能想要使用某种类似于此的表值拆分函数:

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;

然后你可以使用outer apply来加入你的表:

You can then use outer apply to join with 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:

If you want to do this without a split function, then you can use 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

这篇关于在多行上拆分值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！