if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (IP varchar(3), Operate varchar(5)) insert #T select 'IP1','记录1' union all select 'IP1','记录2' union all select 'IP1','记录3' union all select 'IP2','记录4' union all select 'IP2','记录5' union all select 'IP2','记录6' union all select 'IP2','记录7'declare @i int, @max int, @sql varchar(max) select top 1 @i=1, @max=count(*) from #T group by IP order by 2 desc while @i<=@max begin set @sql=isnull(@sql+',','')+'C'+ltrim(@i)+'=max(case i when '+ltrim(@i)+' then Operate else '''' end)' set @i=@i+1 end exec ('select IP,'+@sql+' from (select *,i=(select count(1) from #T where IP=a.IP and Operate<=a.Operate) from #T a) t group by IP') /* IP C1 C2 C3 C4 ---- ----- ----- ----- ----- IP1 记录1 记录2 记录3 IP2 记录4 记录5 记录6 记录7 */
if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (IP varchar(3), Operate varchar(5)) insert #T select 'IP1','记录1' union all select 'IP1','记录2' union all select 'IP1','记录3' union all select 'IP2','记录4' union all select 'IP2','记录5' union all select 'IP2','记录6' union all select 'IP2','记录7'--> 2005 select * from (select id=row_number()over(partition by IP order by IP),* from #T) a pivot (max(Operate) for id in ([1],[2],[3],[4])) b/* IP C1 C2 C3 C4 ---- ----- ----- ----- ----- IP1 记录1 记录2 记录3 IP2 记录4 记录5 记录6 记录7 */
--以下为sql server 2000的写法,sql server 2005的写法小楼兄已给,其他方法见小梁子转我的帖。 create table tb(ip varchar(10), 操作 varchar(10)) insert into tb values('IP1' , '操作1') insert into tb values('IP1' , '操作2') insert into tb values('IP1' , '操作3') insert into tb values('IP2' , '操作4') insert into tb values('IP2' , '操作5') insert into tb values('IP2' , '操作6') insert into tb values('IP2' , '操作7') godeclare @sql varchar(8000) set @sql = 'select ip ' select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 操作 else '' '' end) [列' + cast(px as varchar) + ']' from (select distinct px from (select * , px = (select count(1) from tb where ip = t.ip and 操作 < t.操作) + 1 from tb t) m) as a set @sql = @sql + ' from (select * , px = (select count(1) from tb where ip = t.ip and 操作 < t.操作) + 1 from tb t) n group by ip' exec(@sql) drop table tb/* ip 列1 列2 列3 列4 ---------- ---------- ---------- ---------- ---------- IP1 操作1 操作2 操作3 IP2 操作4 操作5 操作6 操作7 */
create table #T (IP varchar(3), Operate varchar(5))
insert #T
select 'IP1','记录1' union all
select 'IP1','记录2' union all
select 'IP1','记录3' union all
select 'IP2','记录4' union all
select 'IP2','记录5' union all
select 'IP2','记录6' union all
select 'IP2','记录7'declare @i int, @max int, @sql varchar(max)
select top 1 @i=1, @max=count(*) from #T group by IP order by 2 desc
while @i<=@max
begin
set @sql=isnull(@sql+',','')+'C'+ltrim(@i)+'=max(case i when '+ltrim(@i)+' then Operate else '''' end)'
set @i=@i+1
end
exec ('select IP,'+@sql+' from (select *,i=(select count(1) from #T where IP=a.IP and Operate<=a.Operate) from #T a) t group by IP')
/*
IP C1 C2 C3 C4
---- ----- ----- ----- -----
IP1 记录1 记录2 记录3
IP2 记录4 记录5 记录6 记录7
*/
create table #T (IP varchar(3), Operate varchar(5))
insert #T
select 'IP1','记录1' union all
select 'IP1','记录2' union all
select 'IP1','记录3' union all
select 'IP2','记录4' union all
select 'IP2','记录5' union all
select 'IP2','记录6' union all
select 'IP2','记录7'--> 2005
select * from
(select id=row_number()over(partition by IP order by IP),* from #T) a
pivot
(max(Operate) for id in ([1],[2],[3],[4])) b/*
IP C1 C2 C3 C4
---- ----- ----- ----- -----
IP1 记录1 记录2 记录3
IP2 记录4 记录5 记录6 记录7
*/
create table tb(ip varchar(10), 操作 varchar(10))
insert into tb values('IP1' , '操作1')
insert into tb values('IP1' , '操作2')
insert into tb values('IP1' , '操作3')
insert into tb values('IP2' , '操作4')
insert into tb values('IP2' , '操作5')
insert into tb values('IP2' , '操作6')
insert into tb values('IP2' , '操作7')
godeclare @sql varchar(8000)
set @sql = 'select ip '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 操作 else '' '' end) [列' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where ip = t.ip and 操作 < t.操作) + 1 from tb t) m) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where ip = t.ip and 操作 < t.操作) + 1 from tb t) n group by ip'
exec(@sql) drop table tb/*
ip 列1 列2 列3 列4
---------- ---------- ---------- ---------- ----------
IP1 操作1 操作2 操作3
IP2 操作4 操作5 操作6 操作7
*/