一句Sql把縱向表轉為橫向表，並分離分組求均勻和總均勻值。本站提示廣大學習愛好者：（一句Sql把縱向表轉為橫向表，並分離分組求均勻和總均勻值）文章只能為提供參考，不一定能成為您想要的結果。以下是一句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);
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
