--创建测试表及数据
create table tablename_tz(a int,b int)
insert into tablename_tz
select 1,9 union all
select 1,9 union all
select 1,29 union all
select 2,9 union all
select 2,39 union all
select 2,9

declare @sql varchar(8000) set @sql = 'select a '
declare @i int set @i = 1
while ( @i <= 10 ) 
    begin
        set @sql = @sql + ',[' + ltrim(( @i - 1 ) * 10 + 9)
            + ']=(select count(1) from tablename_tz where a= t.a and b='
            + +ltrim(( @i - 1 ) * 10 + 9) + +')'
        set @i = @i + 1
    end
set @sql = @sql + 'from tablename_tz t group by a'

exec (@sql)
/*
A           9           19          29          39          49          59          69          79          89          99
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           2           0           1           0           0           0           0           0           0           0
2           2           0           0           1           0           0           0           0           0           0
*/

------解决方案--------------------

SQL code
create table  t (A int,B int)
insert into T
select 1,9 union all
select 1,9 union all
select 1,29 union all
select 2,9 union all
select 2,39 union all
select 2,9
go
declare @str varchar(max)=''
select @str=@str+',['+cast((number) as varchar(10))+']=(select count(1) from t where A=tb.A and b='+cast((number) as varchar(10))+')' +CHAR(10)
from  master..spt_values b where b.type='p' and b.number<100 AND B.number%10=9
set @str='select A'+@str+' from t tb group by A'
EXEC (@str)
A    9    19    29    39    49    59    69    79    89    99
1    2    0    1    0    0    0    0    0    0    0
2    2    0    0    1    0    0    0    0    0    0

(2 行受影响)