两个表关联汇总，行变列的问题
有两个表，一个员工表，一个是上班或者休息的工作安排表，表结构如下：（每个数据库可能稍微不太一样）
create table employee (
userId int,
userName varchar(20),
primary key (userId)
);
create table shift (
shiftId int,
userId int,
shiftDate date, ----日期
shift varchar(20), ----当天的上班或者休息的类型，如WD(Working Day)/OD(Off Day)/AL(Annual Leave)等等，用缩写。
primary key (shiftId)
);
数据如下：
employee
--------------------------
userId userName
1 eric
2 jacky
3 sandy
4 tommy
shift
----------------------------
shiftId userId shiftDate shift
1 1 2007-1-1 WD
2 1 2007-1-3 WD
3 1 2007-1-6 WD
4 1 2007-1-12 OD
5 2 2007-1-1 WD
6 2 2007-1-5 OD
7 2 2007-1-8 AL
8 4 2007-1-1 AL
我想得到的数据结果为：
userId count(WD) count(AL) count(OD)
1 3 0 1
2 1 0 1
3 0 0 0
4 0 1 1
就是把行经过汇总变成列，请问如何实现，谢谢！！！
[解决办法]
状态应该固定的
seelct a.userId,
sum(case when b.shift= 'WD ' then 1 else 0 end) as [count(WD)],
sum(case when b.shift= 'AL ' then 1 else 0 end) as [count(AL)],
sum(case when b.shift= 'OD ' then 1 else 0 end) as [count(OD)]
from employee a left join shift b
on a.userId=b.userId
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',sum(case b.shift when ' ' '+shift+ ' ' ' then 1 else 0 end) as [count( '+shift+ ')] '
from shift group by shift
set @sql= 'select a.userId '+@sql+ ' from employee a left join shift b on a.userId=b.userId group by a.userId '
exec(@sql)
