日期:2014-05-17 浏览次数:20541 次
CREATE TABLE [dbo].[#test](
dep [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
depId [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
company [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
cost [float] NULL
)
insert into #test values('AC','001','DHL','26')
insert into #test values('AC','001','EMS','221')
insert into #test values('AC','001','SF','312')
insert into #test values('AC','005','EMS','32')
insert into #test values('AC','005','SF','79')
insert into #test values('AI','002','DHL','28')
insert into #test values('AI','002','EMS','223')
insert into #test values('AI','002','TNT','173')
insert into #test values('AI','002','SF','329')
insert into #test values('FI','003','DHL','38')
insert into #test values('FI','003','EMS','233')
insert into #test values('BI','006','SF','79')
select * from #test
--同一个部门可能有不同的depId
select dep,depid,
sum(case when company ='DHL' then cost else 0 end)DHL,
sum(case when company ='EMS' then cost else 0 end)EMS,
sum(case when company ='TNT' then cost else 0 end)TNT,
sum(case when company ='SF' then cost else 0 end)SF,
sum(cost)cost
FROM #test group by dep,depid
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME([company]) + '=max(case when [company]='
+ QUOTENAME([company], '''') + ' then [cost] else 0 end)'
FROM #test
GROUP BY [company]
EXEC('select dep,depid'+@s+' from #test group by dep,depid')