SQL命令

今天看别人写的SQL查询语句，看了半天不知道怎么翻译的。

这个语句一共涉及2张表一张user(username,firm),一张record(username,date,AttendanceState)

firm表示是否可用，0代表可用。attendanceState表示是否出勤，1代表出勤。

现在希望得到的查询结果是：

源代码是这样

declare @s varchar(8000) 
set @s='select  cast(date as datetime) as Date' 
select @s=@s +',['+a.username+']=max(case when username=''+a.username+'' then  attendancestate else 0 end)' 
from (select username from Userinfo where firm=0 ) a 
set @s=@s+' from record group by Date  order by date' 
exec(@s)

这句看不懂

select @s=@s +',['+a.username+']=max(case when username=''+a.username+'' then  attendancestate else 0 end)' 
 from (select username from Userinfo where firm=0 ) a 

所以自己尝试写了这个

  select cast(date as datetime)  as Date
  ,[李华]=MAX(CASE WHEN UserName = '李华' THEN AttendanceState ELSE 0 END )
  ,[李丹]=MAX(CASE WHEN UserName = '李丹' THEN AttendanceState ELSE 0 END )
  , [马少洋]=MAX(CASE WHEN UserName = '马少洋' THEN AttendanceState ELSE 0 END )
  from dbo.Record group by Date  order by date 

做个标识，希望以后有机会回头看看能看懂那些。