SQL 行与列互转

--- 行与列互转

use boris
if OBJECT_ID('tb_ChengJi') is not null
drop table tb_ChengJi
create table tb_ChengJi(Name varchar(10) , Course varchar(10) , Score float) 
insert into tb_ChengJi values('张三' , '语文' , 74) 
insert into tb_ChengJi values('张三' , '数学' , 83) 
insert into tb_ChengJi values('张三' , '物理' , 93) 
insert into tb_ChengJi values('李四' , '语文' , 74) 
insert into tb_ChengJi values('李四' , '数学' , 84)
select * from tb_ChengJi
/*
Name       Course     Score
---------- ---------- -----------
张三         语文         74
张三         数学         83
张三         物理         93
李四         语文         74
李四         数学         84
*/
--简单转换
/*
select distinct Name AS 姓名,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='物理' AND tb_1.Name=tb.Name ) AS 物理 ,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='数学' AND tb_1.Name=tb.Name ) AS 数学 ,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='语文' AND tb_1.Name=tb.Name ) AS 语文 
from tb_ChengJi tb
*/
---PIVOT用于将列值旋转为列名（即行转列） 
select * from tb_ChengJi  pivot (max(Score) for Course in (物理,数学,语文)) AS A
/*
姓名         物理          数学          语文
---------- ----------- ----------- -----------
李四         NULL        84          74
张三         93          83          74
*/
--UNPIVOT列转行
/*
DROP TABLE #TT 
select * INTO #TT FROM 
(select * from tb_ChengJi  pivot (max(Score) for Course in (物理,数学,语文)) AS A) AS T
SELECT * FROM #TT

SELECT Name,Course,Score FROM #TT UNPIVOT (Score for Course in (物理,数学,语文)) AS QQ
*/
--含各人平均分及总分
select Name AS 姓名,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='物理' AND tb_1.Name=tb.Name ) AS 物理 ,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='数学' AND tb_1.Name=tb.Name ) AS 数学 ,
(SELECT Score FROM tb_ChengJi tb_1 WHERE Course='语文' AND tb_1.Name=tb.Name ) AS 语文 ,
CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float) AS 平均分,
--- CONVERT(DECIMAL(18,2),AVG(Score)) AS 平均分,
SUM(Score) AS 总分
from tb_ChengJi tb GROUP BY Name
/*
姓名         物理          数学          语文          平均分         总分
---------- ----------- ----------- ----------- ----------- -----------
李四         NULL        84          74          79.00          158
张三         93          83          74          83.00          250
*/
--加各科平均分
SELECT B.*,C.平均分,C.总分 FROM 
(select * from tb_ChengJi pivot(MAX(Score) for Course in(语文,数学,物理)) AS A) as B,
(SELECT Name,CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float) AS 平均分,CAST(CAST(sum(Score) AS DECIMAL(18,2)) as float) AS 总分  FROM tb_ChengJi GROUP BY Name) AS C
WHERE B.Name=C.Name
UNION all
SELECT DISTINCT '平均值' AS total,
(select CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float)from tb_ChengJi where Course='语文') AS 语文平均成绩,
(select CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float)from tb_ChengJi where Course='数学') AS 数学平均成绩,
(select CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float)from tb_ChengJi where Course='物理') AS 物理平均成绩,
CAST(CAST(AVG(Score) AS DECIMAL(18,2)) as float) AS 平均分,
null
 from tb_ChengJi