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')