--用case when实现
SELECT   a2,
         Count(* ) AS total_a1,
         Sum(CASE 
               WHEN a1%10 = 1 THEN 1
               ELSE 0
             END) AS total_a1_01,
         Sum(CASE 
               WHEN a1%10 = 2 THEN 1
               ELSE 0
             END) AS total_a1_02
FROM     tb
GROUP BY a2

------解决方案--------------------
SQL code

ID A1(int) A2(int)
1 11 100
2 12 100
3 21 100  
4 22 100  
5 22 100
6 23 100
7 11 101
8 22 101
9 12 101

--创建表
IF(OBJECT_ID('A')IS NOT NULL) drop table A
create table a
(
id int,
A1 int,
A2 int
)
go
--插入测试数据
insert into a
select 1,11,100 union all
select 2,12,100 union all
select 3,21,100 union all
select 4,22,100 union all
select 5,22,100 union all
select 6,23,100 union all
select 7,11,101 union all
select 8,22,101 union all
select 9,12,101
--测试插入结果

/*-----------------------------
select * from a
-----------------------------*/
/*
id A1 A2
1 11 100
2 12 100
3 21 100
4 22 100
5 22 100
6 23 100
7 11 101
8 22 101
9 12 101

（所影响的行数为 9 行）
*/

/*
A2    A1    countnum
100    1    2
100    2    3
100    3    1
101    1    1
101    2    2

（所影响的行数为 5 行）
*/

--下面进行行转列

select  A2,
          isnull(sum(case A1 when 0 then isnull(countnum,0) end),0) as total_A1_00,
          isnull(sum(case A1 when 1 then isnull(countnum,0) end),0) as total_A1_01,
          isnull(sum(case A1 when 2 then isnull(countnum,0) end),0) as total_A1_02, 
          isnull(sum(case A1 when 3 then isnull(countnum,0) end),0) as total_A1_03,
          isnull(sum(case A1 when 4 then isnull(countnum,0) end),0) as total_A1_04, 
          isnull(sum(case A1 when 5 then isnull(countnum,0) end),0) as total_A1_05, 
          isnull(sum(case A1 when 6 then isnull(countnum,0) end),0) as total_A1_06, 
          isnull(sum(case A1 when 7 then isnull(countnum,0) end),0) as total_A1_07, 
          isnull(sum(case A1 when 8 then isnull(countnum,0) end),0) as total_A1_08, 
          isnull(sum(case A1 when 9 then isnull(countnum,0) end),0)as total_A1_09
from  (select A2,right(A1,1)as A1,count(*) as countnum from a
    group by A2,right(A1,1)) as b
group by A2
/*
A2          total_A1_00 total_A1_01 total_A1_02 total_A1_03 total_A1_04 total_A1_05 total_A1_06 total_A1_07 total_A1_08 total_A1_09 
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
100         NULL        2           3           1           NULL        NULL        NULL        NULL        NULL        NULL
101         NULL        1           2           NULL        NULL        NULL