MSSQLServer 纵向表转横向表 横向表转纵向表 行转列 列转行

CREATE TABLE Test_y(
[Name] [nchar](10) NULL,
[Course] [nchar](10) NULL,
[Grade] [int] NULL
)
insert into Test_y values ('张三','语文',75);
insert into Test_y values ('张三','数学',80);
insert into Test_y values ('张三','英语',90);
insert into Test_y values ('李四','语文',90);
insert into Test_y values ('李四','数学',70);
insert into Test_y values ('李四','英语',80);CREATE TABLE Test_x(
[Name] [nchar](10) NULL,
[语文] [nchar](10) NULL,
[数学] [nchar](10) NULL,
[英语] [nchar](10) NULL
)
insert into Test_x values('张三',75,80,90);
insert into Test_x values('李四',90,70,80);

select * from Test_y;
select Name,
sum(case Course when '语文' then Grade else 0 end) as 语文,
sum(case Course when '数学' then Grade else 0 end) as 数学,
sum(case Course when '英语' then Grade else 0 end) as 英语
from Test_y group by Name;

select * From Test_y
pivot(sum(Grade) for Course IN ([语文],[数学],[英语]))
AS Test_x;

select * from Test_x;
select Name,'语文' as Course,语文 as Grade
from Test_x union all
select Name,'数学' as Course,数学 as Grade
from Test_x union all
select Name,'英语' as Course,英语 as Grade
from Test_x order by Name desc;

select Name,Course,Grade from Test_x
unpivot(Grade for Course in([数学],[英语],[语文]))as Test_y