SQL 行转列Way1: use several temp tables and then join

最新推荐文章于 2025-12-04 17:50:16 发布

原创最新推荐文章于 2025-12-04 17:50:16 发布 · 482 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #sql #null #table #math #database

本文介绍了一种解决数据库中“行转列”问题的方法。通过创建三个临时表分别存储不同科目的成绩，再将这些表进行连接，最终实现了从原始表格到目标表格的数据转换。

(现在刚学数据库，看到一个题目，自己先做了下，用了三个temp table，很笨拙的方法. 后来才知道这个是 “行转列的问题”，有很简洁的代码，不过不管怎么说，先贴下自己的方法，毕竟是自己辛辛苦苦写的吗，hehe... )

问题:

ID 姓名科目分数
01 a 语文 80
01 a 数学 70
01 a 英语 80
02 b 语文 80
02 b 数学 70
02 b 英语 80

怎样用SQL查出

ID 姓名语文数学英语
01 a 80 70 80
02 b 80 70 80

way1 (of myself:)

Description

Create three temp tables to store every score apart, then join three table

Code

--Create Test DB
Use master
Go
Create database SQLTestTemp
Go

--Create Table
Use SQLTestTemp
Go
Create Table Score01
( ID int Not NULL,
Name nchar(20) Not NULL,
Title nchar(20) Not NULL,
Score int Not NULL
)
Go

--Insert sample data
Use SQLTestTemp
Go
Insert into Score01
(ID, Name, Title, Score)
Values
(01, 'a', 'Chinese', 70),
(01, 'a', 'Math', 80),
(01, 'a', 'English', 90),
(02, 'b', 'Chinese', 75),
(02, 'b', 'Math', 85),
(02, 'b', 'English', 95)
Go

--Check the input
Use SQLTestTemp
Go
Select * from Score01 where Name = 'a'

--Create three temp table
---Chinese Temp table
Use SQLTestTemp
Go
Create Table #ChineseScore
(
ID int Not NULL,
Name nchar(20) Not NULL,
ChineseScore int Not NULL
)
Go

Insert Into #ChineseScore
(ID, Name, ChineseScore)
select ID, Name, Score
From Score01
Where Title = 'Chinese'

Select * from #ChineseScore

---Math Temp Table
Use SQLTestTemp
Go
Create Table #MathScore
(
ID int Not NULL,
Name nchar(20) Not NULL,
MathScore int Not NULL
)
Go

Insert Into #MathScore
(ID, Name, MathScore)
select ID, Name, Score
From Score01
Where Title = 'Math'

Select * from #MathScore

---English Temp Table
Use SQLTestTemp
Go
Create Table #EnglishScore
(
ID int Not NULL,
Name nchar(20) Not NULL,
EnglishScore int Not NULL
)
Go

Insert Into #EnglishScore
(ID, Name, EnglishScore)
select ID, Name, Score
From Score01
Where Title = 'English'

Select * from #EnglishScore

--Get final result
Select S1.ID, S1.Name, S1.ChineseScore As '语文', S2.MathScore As '数学', S3.EnglishScore As '英语'
From #ChineseScore AS S1
Inner Join #MathScore AS S2 On S1.Name = S2.Name
Inner Join #EnglishScore AS S3 On S1.Name = S3.Name