1.创建使用环境
1.1 创建表
CREATE TABLE Score
(
[Name] [nvarchar](10) NULL,
[数学] [decimal](18, 0) null,
[语文] [decimal](18, 0) null,
[英文] [decimal](18, 0) null,
)
1.2 插入基础数据
INSERT Score(name, 数学,语文,英文)
VALUES
('小红','92','93','97'),
('小蓝','91','99','92'),
('小花','98','91','90')
2.行转列 两种方法
2.1使用 UNPIVOT 进行 列转行
完整语法
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
SELECT a.Name,a.CLASS_NAME,a.SCORE FROM
Score
UNPIVOT (CLASS_NAME FOR SCORE IN(数学,语文,英文)) a
2.2 使用UNOIN
SELECT * FROM
(
SELECT NAME,CLASS_NAME='数学',SCORE=[数学] FROM Score
UNION ALL
SELECT NAME,CLASS_NAME='语文',SCORE=[语文] FROM Score
UNION ALL
SELECT NAME,CLASS_NAME='英文',SCORE=[英文] FROM Score
) A
按名字和学科排序
SELECT * FROM
(
SELECT NAME,CLASS_NAME='数学',SCORE=[数学] FROM Score
UNION ALL
SELECT NAME,CLASS_NAME='语文',SCORE=[语文] FROM Score
UNION ALL
SELECT NAME,CLASS_NAME='英文',SCORE=[英文] FROM Score
) A ORDER BY A.NAME,CASE A.CLASS_NAME WHEN '语文' THEN 1
WHEN '英文' THEN 2
WHEN '数学' THEN 3
END