两个表关联汇总，行变列的问题
有两个表，一个员工表，一个是上班或者休息的工作安排表，表结构如下：（每个数据库可能稍微不太一样）
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)