Sql Server数据库表的横纵向输出 聚集函数

1) 纵表变横表

/*创建数据库表*/
use testbase2
create table Table_A
(
    name varchar(20),
    course varchar(20),
    score int
)
insert into Table_A(name,course,score) values('张三','语文',60)
insert into Table_A(name,course,score) values('张三','数学',70)
insert into Table_A(name,course,score) values('张三','英语',80)
insert into Table_A(name,course,score) values('李四','语文',90)
insert into Table_A(name,course,score) values('李四','数学',100)

/*查询插入的语句*/
select  * from Table_A

/*横向输出这张表*/
SELECT name,
sum (case course when '语文' then score else 0 end) as 语文,
sum (case course when '数学' then score else 0 end) as 数学,
sum (case course when '英语' then score else 0 end) as 英语
FROM  Table_A
GROUP BY name

2)横表变纵表

/*纵向输出这张表*/
create table Table_B
(
    name varchar(20),
    chinese int,
    math int,
    english int
)

insert into Table_B(name,chinese,math,english) values('张三',60,70,80)
insert into Table_B(name,chinese,math,english) values('李四',90,100,0)

select * from Table_B

select name,'语文' as 课程,chinese as 成绩 from Table_B union all
select name,'数学' as 课程,math as 成绩 from Table_B union all
select name,'英语' as 课程,english as 成绩 from Table_B
order by name,课程 desc

/查询学生总人数/

select COUNT(name) 总人数
from Table_B

/查询张三的平均成绩/

select avg(score) 平均成绩
from Table_A
where name like '张三';

/查询李四的最高成绩/

select MAX(score) 最高成绩
from Table_A
where name like '李四';

/*查询张三的总成绩*/
select SUM(score) 总成绩
from Table_A
where name like '张三';

/*查询姓名，选课数，总成绩*/
select name 姓名,count(course) 选课数,SUM(score) 总成绩
from Table_A
group by name