如果科目能定下来就可以写成
select 姓名,sum((case 科目1 when xx then 成绩 else 0 end)) ....
from table group by 姓名
否则只能写存储过程用游标或者动态语句去玩了。找一下吧,很多答案的。
select 姓名,sum((case 科目1 when xx then 成绩 else 0 end)) ....
from table group by 姓名
否则只能写存储过程用游标或者动态语句去玩了。找一下吧,很多答案的。
调试欢乐多
GOCREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO下面是用于创建旋转结果的 SELECT 语句:SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
查询姓名和成绩:
Select 姓名,IsNull(成绩,'') 成绩
from 表 a Left join
(Select Distinct 科目 from 表) b
On a.科目=b.科目
order by b.科目,a.姓名
go
insert test_course values ('1','1',60)
insert test_course values ('1','2',60)
insert test_course values ('1','3',60)
insert test_course values ('1','4',60)
insert test_course values ('2','1',60)
insert test_course values ('2','3',60)
insert test_course values ('2','5',60)
insert test_course values ('2','6',60)
godeclare @str varchar(8000)
declare @cause varchar(20)
select @str = 'select 姓名, 'declare my_cursor cursor for
select distinct 科目 from test_courseopen my_cursor
fetch next from my_cursor into @cause
while @@fetch_status = 0
begin
select @str = @str + 'sum((case 科目 when '''+ @cause + ' '' then 成绩 else 0 end)) 科目' + @cause + ','
fetch next from my_cursor
into @cause
endclose my_cursor
deallocate my_cursor
select @str= left(@str,len(@str)-1)
select @str= @str + ' from test_course group by 姓名'
exec( @str )