我的数据是什么样的：
Name: Animals(One-Column):
Bill Dog, Cat, Horse
Bob Dog, Giraffe, Snake , Horse
我需要将其转换为：
Name: Animal:
Bill Dog
Bill Cat
Bill Horse
Bob Dog
Bob Giraffe
Bob Snake
Bob Horse
我不确定要采取的步骤，实际 csv 字段中有 0 - 100 个值。
您会注意到 csv 不一致（马是比尔的第 3 个字，而鲍勃的第 4 个字），所以我需要一个解决方案，例如：
1st Name 1st Word
1st Name 2nd Word
2nd Name 1st Word
2nd Name 2nd Word
2nd Name 3rd Word
...
create function [dbo].[udf_splitstring] (@tokens varchar(max),
@delimiter varchar(5))
returns @split table (
token varchar(200) not null )
as
begin
declare @list xml
select @list = cast('<a>'
+ replace(@tokens, @delimiter, '</a><a>')
+ '</a>' as xml)
insert into @split
(token)
select ltrim(t.value('.', 'varchar(200)')) as data
from @list.nodes('/a') as x(t)
return
end
CREATE TABLE #TABLE1
([NAME] VARCHAR(4), [ANIMALS] VARCHAR(27))
;
INSERT INTO #TABLE1
([NAME], [ANIMALS])
VALUES
('BILL', 'DOG, CAT, HORSE'),
('BOB', 'DOG, GIRAFFE, SNAKE , HORSE')
;
SELECT * FROM #TABLE1
SELECT NAME ,TOKEN FROM #TABLE1 AA
CROSS APPLY (SELECT * FROM UDF_SPLITSTRING(ANIMALS,','))A
输出
name token
Bill Dog
Bill Cat
Bill Horse
Bob Dog
Bob Giraffe
Bob Snake
Bob Horse
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句