declare @str nvarchar(4000)
select @str='select '
select @str = @str + ''''+ a1+''' as a1,''' + a2 + ''' as a2,' from test where a3=0
select @str=left(@str,len(@str)-1)
exec(@str)
select @str='select '
select @str = @str + ''''+ a1+''' as a1,''' + a2 + ''' as a2,' from test where a3=0
select @str=left(@str,len(@str)-1)
exec(@str)
set @sql=''
select @sql=@sql+','+convert(varchar(2),a1)+' as a1,'''+a2+''' as a2' from test where a3=0
select @sql='select '+substring(@sql,2,1000)
exec(@sql)
create table test
(
a1 int,
a2 varchar(8),
a3 int
)insert test select 1,'四川',0
union all select 2,'江苏',0
union all select 3,'广东',0
union all select 4,'云南',1declare @sql nvarchar(1000)
set @sql=''
select @sql=@sql+','+convert(varchar(2),a1)+' as a1,'''+a2+''' as a2' from test where a3=0
select @sql='select '+substring(@sql,2,1000)
exec(@sql)
a1 a2 a1 a2
1 四川 3 广东
2 江苏
(a1 int, a2 nvarchar(10), a3 int)
insert into test
select 1 , '四川' , 0 union all
select 2 , '江苏' , 0 union all
select 3 , '广东' , 0 union all
select 4 , '云南' , 1select identity(int) id,bz=1,* into #t1 from testselect
max(case (a.id-b.id)%3 when 0 then a.a1 else '' end) as a1,
max(case (a.id-b.id)%3 when 0 then a.a2 else '' end) as a2,
max(case (a.id-b.id)%3 when 1 then a.a1 else '' end) as a1,
max(case (a.id-b.id)%3 when 1 then a.a2 else '' end) as a2,
max(case (a.id-b.id)%3 when 2 then a.a1 else '' end) as a1,
max(case (a.id-b.id)%3 when 2 then a.a2 else '' end) as a2
from #t1 a,(select bz ,id=min(id) from #t1 group by bz) b
where a.bz=b.bz
group by a.bz,(a.id-b.id)/3
order by a.bz,(a.id-b.id)/3
a1 a2 a1 a2 a1 a2
----------- ---------- ----------- ---------- ----------- ----------
1 四川 2 江苏 3 广东
4 云南 (所影响的行数为 2 行)select
max(case (a.id-b.id)%2 when 0 then a.a1 else '' end) as a1,
max(case (a.id-b.id)%2 when 0 then a.a2 else '' end) as a2,
max(case (a.id-b.id)%2 when 1 then a.a1 else '' end) as a1,
max(case (a.id-b.id)%2 when 1 then a.a2 else '' end) as a2
from #t1 a,(select bz ,id=min(id) from #t1 group by bz) b
where a.bz=b.bz
group by a.bz,(a.id-b.id)/2
order by a.bz,(a.id-b.id)/2a1 a2 a1 a2
----------- ---------- ----------- ----------
1 四川 2 江苏
3 广东 4 云南(所影响的行数为 2 行)
set @str=''
SELECT @str=@str+''''+a1+'''' +'as a1,'+''''+a2+'''' +'as a2,' FROM TEST where a3=0
set @str=substring(@str,1,len(@str)-1)
exec ('select ' +@str)
1 四川 1
2 江苏 2
3 广东 3
4 云南 4我要得出同样格式的结果 当a3=1、2、3、4的a2的值 并且排成一行 就是这个样子滴:a1 a2 a1 a2 a1 a2 a1 a2
1 四川 2 江苏 3 广东 4 云南请问那个where 后的条件该怎么写呢? 各位哥哥请不吝指教