select 'A' as AA into #a insert into #a select 'B' union all select 'C' select * from #aselect AA1 = (select top 1 AA from #a order by AA),AA2 = (select top 1 AA from #a where AA not in (select top 1 AA from #a order by AA) order by AA), AA3 = (select top 1 AA from #a order by AA desc ) drop table #a
if object_id('tbTest') is not null drop table tbTest GO create table tbTest(AA varchar(10)) insert tbTest select 'A' union all select 'B' union all select 'C'declare @sql varchar(2000),@i int set @sql = '' set @i = 0 select @i = @i + 1, @sql = @sql + ',AA' + rtrim(@i) + '=max(case AA when ''' + AA + ''' then AA end)' from tbTest set @sql = 'select ' + stuff(@sql,1,1,'') + ' from tbTest' EXEC(@sql)drop table tbTest/* 结果 AA1 AA2 AA3 ----------------------- A B C */
create TABLE tab( AA nvarchar(10)) insert into tab select 'A' union select 'B' union select'C'declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'MAX(case AA when '''+AA +''' then AA END )AS AA'+(SELECT cONVERT(NVARCHAR,cOUNT(1)) FROM TAB B WHERE A.AA>=B.AA )+',' from TAB A SET @sql='SELECT '+LEFT(@sql,LEN(@sql)-1)+' FROM TAB' print @sql EXEC(@SQL)AA1 AA2 AA3 ---------- ---------- ---------- A B C
insert into #a
select 'B'
union all select 'C'
select * from #aselect AA1 = (select top 1 AA from #a order by AA),AA2 = (select top 1 AA from #a where AA not in (select top 1 AA from #a order by AA) order by AA),
AA3 = (select top 1 AA from #a order by AA desc )
drop table #a
drop table tbTest
GO
create table tbTest(AA varchar(10))
insert tbTest
select 'A' union all
select 'B' union all
select 'C'declare @sql varchar(2000),@i int
set @sql = ''
set @i = 0
select @i = @i + 1, @sql = @sql + ',AA' + rtrim(@i) + '=max(case AA when ''' + AA + ''' then AA end)'
from tbTest
set @sql = 'select ' + stuff(@sql,1,1,'') + ' from tbTest'
EXEC(@sql)drop table tbTest/*
结果
AA1 AA2 AA3
-----------------------
A B C
*/
insert into tab
select 'A'
union select 'B'
union select'C'declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+'MAX(case AA when '''+AA +''' then AA END )AS AA'+(SELECT cONVERT(NVARCHAR,cOUNT(1)) FROM TAB B WHERE A.AA>=B.AA )+',' from TAB A
SET @sql='SELECT '+LEFT(@sql,LEN(@sql)-1)+' FROM TAB'
print @sql
EXEC(@SQL)AA1 AA2 AA3
---------- ---------- ----------
A B C