sql中常用动态的行转列

//取出源数据
select groupname,totalnum,inputdate,groupid into #temp from
 (select count(*)as totalnum,p.groupid,g.groupname,convert(nvarchar(10),inputdate,120) as 'inputdate'
 from person p left join admin_group g on p.groupid = g.groupid and deleteflag = '0'
 where p.inactive='0' 
 group by p.groupid,g.groupname,convert(nvarchar(10),p.inputdate,120)) a
 left join
 (select a.groupid as areagroupid,b.groupname as area,b.areaid
 from admin_group a inner join admin_group b on a.upperid = b.groupid) b on a.groupid=b.areagroupid
//第一种方式能够控制显示的数据的样式，速度比较快
declare @s nvarchar(max)
select @s = 'select groupname as 社区名,groupid'
select @s = @s + ',isnull(max(case when inputdate=''' + cast(inputdate as varchar(10)) + ''' then totalnum end),0) as ''' + cast(inputdate as varchar(10)) + ''''  from #temp group by inputdate order by inputdate desc
select @s = @s + ' from #temp where groupid=71 group by groupname,groupid'
exec(@s)
//第二种使用pivot（sql2005以上版本中有）
declare @s nvarchar(max)
Select     @s=isnull(@s+',','')+quotename(inputdate) from #temp group by inputdate order by inputdate desc
exec('select * from #temp pivot (max(totalnum) for inputdate in('+@s+'))b')