在SQL Server中从CSV列创建表而不使用游标

Given a table:

给出一张表：

|Name    | Hobbies                |
-----------------------------------
|Joe     | Eating,Running,Golf    |
|Dafydd  | Swimming,Coding,Gaming |

I would like to split these rows out to get:

我想将这些行拆分为：

|Name    | Hobby     |
----------------------
|Joe     | Eating    |
|Joe     | Running   |
|Joe     | Golf      |
|Dafydd  | Swimming  |
|Dafydd  | Coding    |
|Dafydd  | Gaming    |

I have completed this below (example is ready to run in SSMS), buy my solution uses a cursor which I think is ugly. Is there a better way of doing this? I am on SQL Server 2008 R2 if there is anything new which will help me.

我已经在下面完成了这个（示例已准备好在SSMS中运行），购买我的解决方案使用的游标我觉得很难看。有更好的方法吗？我在SQL Server 2008 R2上，如果有任何新的东西可以帮助我。

Thanks

谢谢

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )


go

declare @inputtable table (
    name varchar(200) not null,
    hobbies varchar(200) not null
)

declare @outputtable table (
    name varchar(200) not null,
    hobby varchar(200) not null
)

insert into @inputtable values('Joe', 'Eating,Running,Golf')
insert into @inputtable values('Dafydd', 'Swimming,Coding,Gaming')

select * from @inputtable

declare inputcursor cursor for
select name, hobbies
from @inputtable

open inputcursor

declare @name varchar(255), @hobbiescsv varchar(255)
fetch next from inputcursor into @name, @hobbiescsv
while(@@FETCH_STATUS <> -1) begin

    insert into @outputtable
    select @name, splithobbies.s
    from dbo.split(',', @hobbiescsv) splithobbies

    fetch next from inputcursor into @name, @hobbiescsv 
end
close inputcursor
deallocate inputcursor

select * from @outputtable

4 个解决方案

#1

Use a string parsing function like the one found here. The key is to use CROSS APPLY to execute the function for each row in your base table.

使用像这里找到的字符串解析函数。关键是使用CROSS APPLY来执行基表中每一行的功能。

CREATE FUNCTION [dbo].[fnParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @parsedString TABLE (string NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END
go

declare @MyTable table (
    Name char(10),
    Hobbies varchar(100)
)

insert into @MyTable
    (Name, Hobbies)
    select 'Joe', 'Eating,Running,Golf'
    union all
    select 'Dafydd', 'Swimming,Coding,Gaming'

select t.Name, p.String
    from @mytable t
        cross apply dbo.fnParseStringTSQL(t.Hobbies, ',') p

DROP FUNCTION [dbo].[fnParseStringTSQL]

#2

Just do the following:

只需执行以下操作：

select *
from @inputtable
outer apply dbo.split(',', hobbies) splithobbies

#3

Create this function in your DB:

在您的数据库中创建此功能：

CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))     
returns @temptable TABLE (items varchar(max))     
as     
begin     
    declare @idx int     
    declare @split varchar(max)     

    select @idx = 1     
        if len(@origString )<1 or @origString is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@origString)     
        if @idx!=0     
            set @split= left(@origString,@idx - 1)     
        else     
            set @split= @origString

        if(len(@split)>0)
            insert into @temptable(Items) values(@split)     

        set @origString= right(@origString,len(@origString) - @idx)     
        if len(@origString) = 0 break     
    end 
return     
end

and then simply call it in your Select statement and use cross apply to join to the function

然后只需在Select语句中调用它，并使用cross apply连接到该函数

Select t.Name, 
       s.items as 'Hobby'
from dbo.MyTable as t
Cross Apply dbo.Split(t.Hobbies,',') as s

#4

I generally prefer to use XML to split CSV list to table valued format. You can check this function:

我通常更喜欢使用XML将CSV列表拆分为表格值格式。你可以检查这个功能：

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

and the following article for more techniques showing how to do this. Then, you just need to use CROSS APPLY clause to apply the function.

以及下面的文章，了解如何执行此操作的更多技巧。然后，您只需要使用CROSS APPLY子句来应用该函数。

#1