在SQL Server中从CSV列创建表,而不使用游标 [英] Create a table from CSV columns in SQL Server without using a cursor
本文介绍了在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中运行),购买我的解决方案使用一个光标我认为是丑陋的。有没有更好的方法这样做?
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.
感谢
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
推荐答案
使用字符串解析函数,如此处。关键是使用 CROSS APPLY 为基表中的每一行执行函数。
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.
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]
这篇关于在SQL Server中从CSV列创建表,而不使用游标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文