• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

一句Sql把纵向表转为横向表,并分别分组_sqlserver

sqlserver 搞代码 3年前 (2018-06-17) 218次浏览 已收录 0个评论

效果如图所示:
一句Sql把纵向表转为横向表,并分别分组
测试sql语句如下:

复制代码 代码如下:
declare @tab table(Class varchar(20),Student varchar(20),Course varchar(50),Quantity decimal(7,2));
insert into @tab(Class,Student,Course,Quantity) values(“A班”,”张三”,”语文”,60);

http://www.gaodaima.com/?p=37550一句Sql把纵向表转为横向表,并分别分组_sqlserver

insert into @tab(Class,Student,Course,Quantity) values(“A班”,”张三”,”数学”,70);
insert into @tab(Class,Student,Course,Quantity) values(“A班”,”张三”,”英语”,80);
insert into @tab(Class,Student,Course,Quantity) values(“A班”,”李四”,”语文”,30);
insert into @tab(Class,Student,Course,Quantity) values(“A班”,”李四”,”数学”,40);
insert into @tab(Class,Student,Course,Quantity) values(“A班”,”李四”,”英语”,50);

insert into @tab(Class,Student,Course,Quantity) values(“B班”,”王五”,”语文”,65);
insert into @tab(Class,Student,Course,Quantity) values(“B班”,”王五”,”数学”,75);
insert into @tab(Class,Student,Course,Quantity) values(“B班”,”王五”,”英语”,85);
insert into @tab(Class,Student,Course,Quantity) values(“B班”,”赵六”,”语文”,35);
insert into @tab(Class,Student,Course,Quantity) values(“B班”,”赵六”,”数学”,45);
insert into @tab(Class,Student,Course,Quantity) values(“B班”,”赵六”,”英语”,55);

select * from @tab

select
(case when Grouping(Class)=1 then “总平均” when Grouping(Student)=1 then “” else Class end ) as Class
,(case when Grouping(Class)=1 then “” when Grouping(Student)=1 then “平均” else Student end) as Student
,avg(语文) as 语文
,avg(数学) as 数学
,avg(英语) as 英语
,avg(总分) as 总分
from (
select Class,Student
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=”语文”) as “语文”
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=”数学”) as “数学”
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student and Course=”英语”) as “英语”
,(select isnull(sum(Quantity),0) from @tab where Class=t.Class and Student=t.Student) as “总分”
from @tab as t
group by Class,Student
) as tempTab
group by Class,Student,语文,数学,英语,总分 with rollup
having Grouping(语文)=1
and Grouping(数学)=1
and Grouping(英语)=1

欢迎大家阅读《一句Sql把纵向表转为横向表,并分别分组_sqlserver》,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址