行列转换统计

create table tbl1(name varchar(10), book_id varchar(10))
insert into tbl1 values('张三' ,   '001')
insert into tbl1 values('李四' ,   '002')
insert into tbl1 values('李四' ,   '001')
insert into tbl1 values('张三' ,   '002')
insert into tbl1 values('张三' ,   '002')
insert into tbl1 values('王五' ,   '001')
insert into tbl1 values('王五' ,   '001')
insert into tbl1 values('王五' ,   '002')
insert into tbl1 values('赵六' ,   '001')
create table tbl2(book_id varchar(10), book_name varchar(10))
insert into tbl2 values('001' ,     '教材1')
insert into tbl2 values('002' ,     '教材2')
go

--sql 2000静态SQL，指tbl2book_name固定为'教材1','教材2'
select m.name,
  sum(case n.book_name when '教材1' then 1 else 0 end ) [教材1数量],
  sum(case n.book_name when '教材2' then 1 else 0 end ) [教材2数量],
  count(*) 教材总数
from tbl1 m, tbl2 n
where m.book_id = n.book_id
group by m.name

/*
name       教材1数量       教材2数量       教材总数       
---------- ----------- ----------- -----------
李四         1           1           2
王五         2           1           3
张三         1           2           3
赵六         1           0           1

（所影响的行数为 4 行）
*/
--sql 2000动态SQL，指tbl2book_name不固定
declare @sql varchar(8000)
set @sql = 'select m.name '
select @sql = @sql + ' , sum(case n.book_name when ''' + book_name + ''' then 1 else 0 end) [' + book_name + '数量]'
from (select distinct book_name from tbl2) as a
set @sql = @sql + ' , count(*) 教材总数 from tbl1 m , tbl2 n where m.book_id = n.book_id group by m.name'
exec(@sql)
/*
name       教材1数量       教材2数量       教材总数       
---------- ----------- ----------- -----------
李四         1           1           2
王五         2           1           3
张三         1           2           3
赵六         1           0           1

*/
drop table tbl1 , tbl2