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
3
You can use CROSS APPLY join:
你可以使用CROSS APPLY join:
SELECT Column1, Data
FROM MYTABLE CROSS APPLY dbo.Split(Column2, ',')
#1
3
You can use CROSS APPLY join:
你可以使用CROSS APPLY join:
SELECT Column1, Data
FROM MYTABLE CROSS APPLY dbo.Split(Column2, ',')