我有列 col1 和 col2
Col1 col2
abc,def xyz,xyz
abc1,def1 xyz1,xyz1
我需要如下输出
Col1 col2
abc xyz,xyz
def xyz,xyz
abc1 xyz1,xyz1
def1 xyz1,xyz1
也就是说,如果 col1 包含 2 个值 (abc,def) 而 col2 包含 2 个值 (xyz,xyz) 那么我需要 4 行。同样 col1 和 col2 包含 2 个值,那么我需要 9 行。
请帮我在sql server中获取输出
一点点 XML 和一个 CROSS APPLY
选项 1:没有拆分/解析函数
Declare @YourTable table (Col1 varchar(25),col2 varchar(25))
Insert Into @YourTable values
('abc,def','xyz,xyz'),
('abc1,def1','xyz1,xyz1')
Select col1 = B.RetVal
,col2 = A.col2
From @YourTable A
Cross Apply (
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((Select replace(A.Col1,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
Cross Apply x.nodes('x') AS B(i)
) B
退货
col1 col2
abc xyz,xyz
def xyz,xyz
abc1 xyz1,xyz1
def1 xyz1,xyz1
选项 2:使用拆分/解析功能
Select col1 = B.RetVal
,col2 = A.col2
From @YourTable A
Cross Apply [dbo].[udf-Str-Parse](A.col1,',') 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((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句