declare @a table(id int,username char(10),suject char(10),score int)
insert into @a
select 1,'张三','语文', 70 union
select 1,'张三','数学', 80 union
select 1,'张三','英语', 90 union
select 2,'李四','数学', 65select distinct a.id,a.username,
语文=(select score from @a where username=a.username and id=a.id and suject='语文'),
数学=(select score from @a where username=a.username and id=a.id and suject='数学'),
英语=(select score from @a where username=a.username and id=a.id and suject='英语')
from @a a --结果如下
--id username 语文 数学 英语
--------------------------------------------
--1 张三 70 80 90
--2 李四 65
insert into @a
select 1,'张三','语文', 70 union
select 1,'张三','数学', 80 union
select 1,'张三','英语', 90 union
select 2,'李四','数学', 65select distinct a.id,a.username,
语文=(select score from @a where username=a.username and id=a.id and suject='语文'),
数学=(select score from @a where username=a.username and id=a.id and suject='数学'),
英语=(select score from @a where username=a.username and id=a.id and suject='英语')
from @a a --结果如下
--id username 语文 数学 英语
--------------------------------------------
--1 张三 70 80 90
--2 李四 65
jxdjxd1111(qqq) 好现实阿!!
union all select 1,'張三','數學',80
union all select 1,'張三','英語',90
union all select 2,'李四','數學',65select id,username,max(case subject when '語文' then score else 0 end) as 語文,max(case subject when '數學' then score else 0 end) as 數學,
max(case subject when '英語' then score else 0 end) as 英語 from @tbltest
group by id,usernameorder by id
declare @cSql varchar(2000)
insert into #tbltest select 1,'張三','語文',70
union all select 1,'張三','數學',80
union all select 1,'張三','英語',90
union all select 2,'李四','數學',65select @cSQL='select id,username '
select @cSQL=@cSQL+' ,max(case subject when '''+subject+''' then score else 0 end) as ['+subject+']'
from (select distinct subject from #tbltest) as aselect @csql=@csql+' from #tbltest group by id,username order by id'--select @csql
exec(@csql)
(select grade
from choseclass
where classNo='001'
and studentNo=A.studentNo
) as 零零一课成绩,
max(case classNo
when '002' then grade
else null
end) as 零零二课成绩
from choseclass A
group by studentNo