SQL Server数据行转列案例实践

数据库某表行转列案例实践

--第一步，创建测试表[StudentsScore]及测试数据

CREATE TABLE [dbo].[StudentsScore](
 [Student] [varchar](10) NOT NULL,
 [Subject] [varchar](10) NOT NULL,
 [Score] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生A', N'中文', 80)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生A', N'数学', 90)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生A', N'英语', 95)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生B', N'中文', 89)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生B', N'数学', 86)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生B', N'英语', 90)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生C', N'中文', 75)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生C', N'数学', 86)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生C', N'英语', 90)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生D', N'中文', 86)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生D', N'数学', 98)
INSERT [dbo].[StudentsScore] ([Student], [Subject], [Score]) VALUES (N'学生D', N'英语', 85)

实现效果如下

--第二步，用两种方法实现行转列

 

--方法一：利用sql2005中的新功能 PIVOT(可以将数据值转换为数据列)
select A.*,B.[总分],B.[平均分]
from
(select Student,[中文],[数学],[英语]
from
(select * from StudentsScore) as PS
PIVOT
(max(score)
for [Subject] in([中文],[数学],[英语])
)as pivotTable) A,
(select Student,SUM(score)[总分],cast(AVG(score*1.0)as decimal(18,2))[平均分] from StudentsScore group by Student) B where A.Student=B.Student

--方法二：用传统的case方法
select Student,
max(case [subject] when '中文' then Score else 0 end) [中文],
max(case [subject] when '数学' then Score else 0 end) [数学],
max(case [subject] when '英语' then Score else 0 end) [英语],
SUM(Score)[总分],
cast(AVG(score*1.0)as decimal(18,2))[平均分]
from StudentsScore group by Student