日期:2014-05-17 浏览次数:20534 次
--参考
if object_id('#test') is not null
drop table #test
go
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
go
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')
CREATE TABLE [dbo].[#test1](
[姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[科目] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[排名] [nvarchar](50) NULL
)
insert into #test1 values('张三','语文','第一')
insert into #test1 values('张三','数学','第二')
insert into #test1 values('张三','英语','第一')
insert into #test1 values('李四','语文','第三')
insert into #test1 values('李四',