sql-server – 在SQL Server中从CSV列创建一个表,而不使用游标

给一张桌子：

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

我想把这些行拆分出来：

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

我已经完成了下面的例子(例子已经准备好运行在SSMS),买我的解决方案使用我认为是丑陋的光标.有没有更好的方法呢？我在sql Server 2008 R2上,如果有什么新的,将帮助我.

谢谢

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,@s,stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,SUBSTRING(@s,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,Golf')
insert into @inputtable values('Dafydd','Swimming,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

解决方法

使用一个字符串解析函数,就像找到的一个字符串 here.关键是使用 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,@position) - @position)
         SET @position = charindex(@separator,@position) + 1
      END
     RETURN
END
go

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

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

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

DROP FUNCTION [dbo].[fnParseStringTsql]

sql-server – 在SQL Server中从CSV列创建一个表,而不使用游标

解决方法

猜你在找的MsSQL相关文章