我们需要在sql server中拆分两列的数据。
CREATE TABLE #t (Id int,Number varchar(32),Name varchar(32));
Insert Into #t(Id, Number,Name)
Values(123, '1,2,3','a~b~c') ,
(456, '1,2,3,4','a~b~c~d'),
(789, '1,2','a~b')
SELECT *FROM #t
输入:Id Number Name
123 1,2,3 a~b~c
456 1,2,3,4 a~b~c~d
789 1,2 a~b
我们需要以下格式的输出:
Id Number Name
123 1 a
123 2 b
123 3 c
456 1 a
456 2 b
456 3 c
456 4 d
789 1 a
789 2 b
下面是使用cte..
的方法;WITH cte_1
AS
(SELECT Id,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))))RNO
,LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Number
FROM
(
SELECT Id
,CAST('<XMLRoot><RowData>' + REPLACE(Number,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM #t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n))
,cte_2
AS
(SELECT Id,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))))RNO
,LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Name
FROM
(
SELECT Id,CAST('<XMLRoot><RowData>' + REPLACE(Name,'~','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM #t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n))
SELECT a.id,a.Number,b.name
FROM cte_1 a JOIN cte_2 b
ON a.id=b.id and a.RNo=b.RNo
借助解析器和Cross Apply
Select A.ID
,B.*
From #t A
Cross Apply ( Select Number=C.RetVal
,Name =D.RetVal
From (Select * from [dbo].[udf-Str-Parse](A.Number,',')) C
Join (Select * from [dbo].[udf-Str-Parse](A.Name,'~')) D
on (C.RetSeq=D.RetSeq)
) B
ID Number Name
123 1 a
123 2 b
123 3 c
456 1 a
456 2 b
456 3 c
456 4 d
789 1 a
789 2 b
如果需要的话UDF
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
编辑如果不能使用UDF -我们可以将UDF代码移动到两个子查询中以产生相同的结果
Select A.ID
,B.*
From #t A
Cross Apply ( Select Number=C.RetVal
,Name =D.RetVal
From (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ Replace(A.Number,',','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) C
Join (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ Replace(A.Name,'~','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) D
on (C.RetSeq=D.RetSeq)
) B
使用XML和cte的另一种方式:
DECLARE @x xml
SELECT @x = (
SELECT Id as '@id',
CAST('<n number="'+REPLACE(Number,',','"/><n number="')+'"/>' as xml),
CAST('<w name="'+REPLACE([Name],'~','"/><w name="')+'" />' as xml)
FROM #t
FOR XML PATH('i')
)
;WITH numbers AS (
SELECT t.v.value('../@id','int') as id,
t.v.value('@number','nvarchar(10)') as number,
ROW_NUMBER() OVER (PARTITION BY t.v.value('../@id','int') ORDER BY (SELECT NULL)) as RN
FROM @x.nodes('/i/n') as t(v)
), names AS (
SELECT t.v.value('../@id','int') as id,
t.v.value('@name','nvarchar(10)') as [name],
ROW_NUMBER() OVER (PARTITION BY t.v.value('../@id','int') ORDER BY (SELECT NULL)) as RN
FROM @x.nodes('/i/w') as t(v)
)
SELECT n.id,
n.number,
nn.[name]
FROM numbers n
INNER JOIN names nn
ON n.id = nn.id and n.rn = nn.rn
输出:id number name
123 1 a
123 2 b
123 3 c
456 1 a
456 2 b
456 3 c
456 4 d
789 1 a
789 2 b
我在ROW_NUMBER()部分中使用SELECT NULL
来原样获取排序。但是它可能会导致一些问题,比如排序不正确。