--> 测试数据：@tb
declare @tb table([id] int,[teamid] int,[team] varchar(6),[carcode] varchar(5),[shuliang] numeric(7,4),[shijian] datetime)
insert @tb
select 1,1,'厂一队','00001',100.0000,'2012-6-12 0:00:00' union all
select 2,1,'厂一队','00001',200.0000,'2012-6-13 0:00:00' union all
select 3,2,'厂二队','00002',300.0000,'2012-6-12 0:00:00' union all
select 4,2,'厂二队','00002',400.0000,'2012-6-13 0:00:00' union all
select 5,1,'厂一队','00002',100.0000,'2012-6-12 0:00:00' union all
select 6,1,'厂一队','00002',200.0000,'2012-6-13 0:00:00' union all
select 7,3,'厂三队','00010',500.0000,'2012-6-12 0:00:00'

--SQL语句
--得到每个team,carcode的当天及累计数量
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY teamid,team ORDER BY teamid), 
    teamid,team,carcode,
    SUM(CASE WHEN shijian >= CONVERT(varchar(10),GETDATE(),120) 
                  AND shijian < CONVERT(varchar(10),DATEADD(day,1,GETDATE()),120)
              THEN shuliang ELSE 0 END) AS CurrentDay,
       SUM(shuliang) AS total INTO #TMP
FROM @tb
GROUP BY teamid,team,carcode;


--得到一共有多少个team,并生成列字符串
DECLARE @s varchar(MAX);
SET @s='';

SELECT @s=@s+',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN carcode ELSE '''' END) AS ['+team+']'
            +',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(CurrentDay AS numeric(12,2))) ELSE '''' END) AS [' + team+'_当天]'
            +',MAX(CASE WHEN teamid='+RTRIM(teamid)+' THEN RTRIM(CAST(total AS numeric(12,2))) ELSE '''' END) AS [' + team+'_累计]'
FROM #TMP
GROUP BY teamid,team
ORDER BY teamid;

--执行
SET @s='SELECT '+STUFF(@s,1,1,'')+' FROM #TMP GROUP BY rowid';
PRINT @s;
EXEC(@s);

--删除临时表
DROP TABLE #TMP;


/*
厂一队    厂一队_当天    厂一队_累计    厂二队    厂二队_当天    厂二队_累计    厂三队    厂三队_当天    厂三队_累计
00001    200.00    300.00    00002    400.00    700.00    00010    0.00    500.00
00002    200.00    300.00                        
*/