34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-15 23:16:48
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([学号] int,[课程] varchar(1),[分数] int)
insert [huang]
select 1,'A',90 union all
select 2,'B',30 union all
select 1,'C',67
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename('课程'+[课程])+'=sum(case when [课程]='+quotename([课程],'''')+' then [分数] else 0 end)'
from [huang] group by [学号],[课程]
exec('select [学号]'+@s+' from [huang] group by [学号]')
----------------结果----------------------------
/*
学号 课程A 课程C 课程B
----------- ----------- ----------- -----------
1 90 67 0
2 0 0 30
(2 行受影响)
*/