問題描述

我有一個如下所示的數據集(輸入).

I have a data set that looks like the below (the input).

IR#   CR#
1     1,2
2     3
3     4,5,6

我想要以下輸出.對于本示例，您可以考慮所有字段 varchar.

I would like the following output. You can consider all fields varchar for this example.

IR#   CR#
1     1
1     2
2     3
3     4
3     5
3     6

我有 UDF 將 CSV 字符串拆分為行...但不能將表中的 1 行拆分為多行，然后聯合將下一行等.

I have UDFs to split a CSV string into rows...but not something to split 1 row in a table into multiple rows and then union will the next row, etc.

謝謝！

推薦答案

使用 CROSSAPPLY 與您的拆分 UDF 結合使用.我在示例中使用的字符串拆分器來自 此處.

Use CROSS APPLY in conjunction with your splitting UDF. The string splitter I'm using for my example comes from here.

/* Create function for purposes of demo */
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

/* Set up sample data */
declare @t table (
    IR int,
    CR varchar(100)
)

insert into @t
    (IR, CR)
    select 1, '1,2' union all
    select 2, '3' union all
    select 3, '4,5,6'

/* Here's the query that solves the problem */
select t.IR, p.string
    from @t t
        cross apply [dbo].[fnParseStringTSQL](t.CR,',') p


/* clean up after demo */
drop function [dbo].[fnParseStringTSQL]

這篇關于SQL Server 2008 從記錄中的字段拆分字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！