您想脱离循环是正确的。
由于您在2012年,因此String_Split()不在桌面上。但是,野生中有任意数量的拆分/解析TVF函数。
Declare @T1 table (Brand varchar(50))
Insert Into @T1 values
('ACER,ASUS,HP'),
('AMD,NVIDIA,SONY')
Declare @T2 table (Brand varchar(50))
Insert Into @T2 values
('HP,GIGABYTE'),
('MICROSOFT'),
('SAMSUNG,PHILIPS')
Select Distinct
T1_Brand = A.Brand
,T2_Brand = B.Brand
From (
Select Brand,B.*
From @T1
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace(Brand,',','</x><x>')+'</x>' as xml)) as A
Cross Apply x.nodes('x') AS B(i)
) B
) A
Join (
Select Brand,B.*
From @T2
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace(Brand,',','</x><x>')+'</x>' as xml)) as A
Cross Apply x.nodes('x') AS B(i)
) B
) B
on A.RetVal=B.RetVal
Select Distinct
T1_Brand = A.Brand
,T2_Brand = B.Brand
From (
Select Brand,B.*
From @T1
Cross Apply [dbo].[tvf-Str-Parse](Brand,',') B
) A
Join (
Select Brand,B.*
From @T2
Cross Apply [dbo].[tvf-Str-Parse](Brand,',') B
) B
on A.RetVal=B.RetVal
T1_Brand T2_Brand
ACER,ASUS,HP HP,GIGABYTE
CREATE FUNCTION [dbo].[tvf-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 A
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[tvf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[tvf-Str-Parse]('this,is,<test>,for,< & >',',')