Question

SQL 语句 如何把多行数据放入一行显示 比如：

SID FID MID Score
----------- ---------- ---------- -----------
1 1 T001 80
2 2 T001 72
3 3 T001 68
4 4 T001 88
5 1 T002 65
6 2 T002 98
7 3 T002 78
8 4 T002 72
9 1 T003 66
10 2 T003 76

如何得到
T001 80 72 68 88
T002 68 98 78 72
......

Answer 1

楼上的有道理，但是非常不灵活，如果得到的结果是在程序里显示的话，可以取出结果集在程序里处理，这个简单，就不说了。如果是想一条语句实现的话，就需要用到sql函数了，可以自己写个函数，代码如下：
if exists(select * from sysobjects where name='Fgetscore' and xtype='FN')
begin
drop function Fgetscore
end
go
create function Fgetscore(@mid as nvarchar(20))
returns nvarchar(2000)
as
begin
declare @sscore nvarchar(2000)
declare @nscore as float

declare cur1 cursor for
select score from tablename where mid=@mid
set @sscore=@mid

open cur1
fetch next from cur1 into @nscore
while @@fetch_status=0 --循环取出score
begin
set @sscore=@sscore + ' ' + cast(@nscore as nvarchar(10))
fetch next from cur1 into @nscore
end
return @sscore
end
go
创建好函数后，像如下调用就可以了（切记“dbo.”不能省略）：
select dbo.Fgetscore(mid) as scores from tablename group by mid

Answer 2

如果FID是固定的只有1,2,3,4，可以用下面的sql实现
select MID,
sum(case when fid = 1 then score else null end) score1,
sum(case when fid = 2 then score else null end) score2,
sum(case when fid = 3 then score else null end) score3,
sum(case when fid = 4 then score else null end) score4
from tablename
group by MID

Answer 3

select A.MID,MAX(A.FID1),MAX(A.FID2),MAX(A.FID3),MAX(A.FID4)
(SELECT MID,(case when FID=1 THEN Score else -1 end) as FID1
,(case when FID=2 THEN Score else -1 end) as FID2
,(case when FID=3 THEN Score else -1 end) as FID3
,(case when FID=4 THEN Score else -1 end) as FID4
FROM TableName
) A
GROUP BY A.MID

Answer 4

select
decode(mid,'T001',score),
decode(mid,'T002',score)
from yourtablename;