22,207
社区成员
发帖
与我相关
我的任务
分享
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
set nocount on;
Create table #T([ID] int,[ColA] nvarchar(1),[ColB] int,[Value] nvarchar(4))
Insert #T
select 1,N'A',1,N'"A1"' union all
select 2,N'A',2,N'"A2"' union all
select 3,N'B',3,N'"B3"' union all
select 4,N'B',4,N'"B4"'
Go
declare @s nvarchar(100),@sql nvarchar(2000),@i int
set @s='A,1,B,4'
----
--处理
select @s=@s+',',@Sql=''
set @i=1
while charindex(',',@s)>0
select @sql=@sql+case when @i&1=1 and @i>2 then ' union all ' else '' end+
case when @i&1=1 then ' select * from #T where [ColA]='''+left(@s,charindex(',',@s)-1)+''''
else ' and [ColB]='+left(@s,charindex(',',@s)-1) end,
@s=stuff(@s,1,charindex(',',@s),''),@i=@i+1
exec( @sql)
/*
ID ColA ColB Value
----------- ---- ----------- -----
1 A 1 "A1"
4 B 4 "B4"
*/