列变行

create table test
(RssID int,
 RssName varchar(20),
 Tid int)
insert test
select 1,'新闻',44792 union all
select 1,'新闻',62732 union all
select 1,'新闻',88147 union all
select 2,'社会',23231 union all
select 2,'社会',17452 union all
select 3,'足坛',22181 union all
select 3,'足坛',61333 union all
select 3,'足坛',32211

select *,id=IDENTITY(int) into # from test

declare @sql varchar(8000),@maxcnt int,@temp int
select @sql='',@temp=1,@maxcnt=max(cnt) from(select count(*) cnt from # group by RssID,RssName) as a
--返回最大列值
/*????
''   1    3
*/
print @sql
print @temp
print @maxcnt

--设置最大列
while(@temp<=@maxcnt)
begin
set @sql=@sql+',isnull(max(case when Tid_cnt='+rtrim(@temp)
             +' then Tid else null end),''8'') as [Tid'+rtrim(@temp)+']'
set @temp=@temp+1
end
print @sql

exec ('select RssID,RssName'+@sql+' from (select RssID,RssName,Tid,(select count(*) from # where RssID=a.RssID and RssName=a.RssName and id<=a.id)[Tid_cnt] from # a)a group by RssID,RssName order by RssID')

 

--select RssID,RssName,Tid,(select count(*) from # where RssID=a.RssID and RssName=a.RssName and id<=a.id) as [Tid_cnt] from # a
select RssID,RssName,Tid,(select count(*) from # where RssID=a.RssID and RssName=a.RssName and id<=a.id) as [Tid_cnt] from # a
select RssID,RssName,Tid [Tid_cnt] from # a
select count(*) from # a where RssID=a.RssID and RssName=a.RssName

select (select count(*) from # where  RssID=a.RssID  and id<=a.id) as [Tid_cnt] from # a