SQL Server：将数据连接成表格格式

I am using SQL Server 2008. I have a split function called func_SPLIT(@Str as varchar(2000)) that will take a string like "123, 456, 789" and split them into a table format like:

我正在使用SQL Server 2008.我有一个名为func_SPLIT(@Str as varchar(2000))的拆分函数,它将采用类似“123,456,789”的字符串,并将它们拆分为表格格式,如:

Column: 
123
456
789

So, if I select one single record, I can split that string to the table format... the join them later....

所以,如果我选择一个单独的记录,我可以将该字符串拆分为表格格式...稍后加入它们....

Here is a problem that I am trying to figure out how to do.

这是一个我试图弄清楚如何做的问题。

Let's say, I have a query:

比方说,我有一个问题:

Select Column1, Column2 
from Table1

and it returns multiple records as below:

并返回多个记录,如下所示:

Column1     Column2
a           11111, 22222, 33333
b           44444
c           55555, 66666
d           88888, 99999

Is there a way I can convert all these values in Column2 into a table format, so I can join them later... like this format below:

有没有办法可以将Column2中的所有这些值转换为表格格式,所以我可以稍后加入它们...就像下面这样的格式:

Column1     Column2
a           11111
a           22222
a           33333
b           44444
c           55555
c           66666
d           88888
d           99999

Here is the split funcion

这是分裂的功能

ALTER FUNCTION [dbo].[Split]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END

Thank you,

1 个解决方案

#1

You can use CROSS APPLY join:

你可以使用CROSS APPLY join:

SELECT Column1, Data
FROM MYTABLE CROSS APPLY dbo.Split(Column2, ',')

Demo: http://sqlfiddle.com/#!3/4e15d/1