将字符串列拆分为多个记录 [英] split string column into multiple records

问题描述

我需要将数千个源记录转换为多个记录，如下端所示，其形式如下：



列A列B


record1 12,13,14,18



此类源记录的结果应转换为以下4记录下面。意味着：应为列B中的每个逗号分隔数创建一条记录。列B中的字符串包含不同数量的逗号分隔数字。有时4个数字有时25个数字。记录转换的结果应该像这样查找上面的例子：



列A列B



record1 12



record1 13



record1 14



record1 18



i不想使用while循环。

I need to transform thousands of source records into multiple records as shown in the bottom end, that have the following form:

column A Column B

record1 12,13,14,18

the result of such source records should get transformed into the following 4 records down below. Means: A record should get created for every comma delimited number in column B. The string in column B contains different amount of comma delimited numbers. sometimes 4 numbers sometimes 25 numbers. Result of the record transformations should look for the example above like this:

column A column B

record1 12

record1 13

record1 14

record1 18

i dont want to use while loop.

推荐答案

如果你有适当版本的SQL Server你可以显然使用递归... http：/ /stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows [ ^ ]

我在VS2005上尝试了这个并且没有为我工作，但原则可以扩展为我猜的功能。然而，需要解决方案的那个人将此标记为答案

If you have an appropriate version of SQL Server you can apparently use recursion ... http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows[^]
I've tried this on VS2005 and it doesn't work for me but the principle could be expanded into a function I guess. Nevertheless the chap that needed the solution marked this as an answer

您好，



检查以下链接是否有分离功能..那么你将使用一个变量获取ColumnA值并放入最终的Select语句中。

赞

Hi,

Check the following link for Split Function... then u will use one variable for Get the ColumnA value and place in final Select Statement.
Like

DECLARE @ColA VARCHAR(1000), @ColB VARCHAR(1000)
SELECT @ColA = columnA, @ColB = columnB  FROM Table_Name
SELECT @ColA,ColumnValues FROM fnSplit(@ColB ,',')

如果有任何澄清，请检查以下链接

分割功能错误 - 声明终止。在语句完成之前，最大递归100已经耗尽 [ ^ ]



问候，

GVPRabu

If any Clarifications, Check the following link
Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]

Regards,
GVPRabu

这篇关于将字符串列拆分为多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！