创建函数以拆分表中所有记录的分隔数据 [英] Create function to split delimited seperated data for all records in the table

问题描述

我有这样的输入-

select ID,FIELD from TABLE

1| A,B,C,D
2|X,Y,Z

像这样输出-

SELECT ID,FIELD from TABLE

1|A
1|B
1|C
1|D
2|X
2|Y
2|Z

有人可以帮助我吗，我怎样才能在 SQL Server 2014 中以简单的方式做到这一点?

Could someone please help me as how can I do it in SQL Server 2014 in an easy way ?

推荐答案

您可以从 Aaron Bertrand 的 以正确的方式或次佳的方式拆分字符串，并与 cross apply 从表中选择数据.对于本演示，我选择使用 XML 字符串拆分功能.
首先，创建函数:

You can choose a string splitting function from Aaron Bertrand's Split strings the right way – or the next best way, and use it with cross apply to select the data from your table. For this demonstration I've chosen to go with the XML string split function.
So first, create the function:

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

那么你需要做的就是这样:

Then all you need to do is something like this:

SELECT ID, Item
FROM TABLE
CROSS APPLY
dbo.SplitStrings_XML(FIELD, ',')

观看关于 rextester 的现场演示.

另外，你应该阅读在数据库列中存储分隔列表真的有那么糟糕吗?，您会在这里看到很多原因，为什么这个问题的答案是绝对是！

Also,you should probably read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!

这篇关于创建函数以拆分表中所有记录的分隔数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！