SQL Server数据行转列案例实践
数据库某表行转列案例实践
--第一步，创建测试表[StudentsScore]及测试数据
CREATE TABLE [dbo].[StudentsScore]( [Student] varchar NOT NULL, [Subject] varchar 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(score1.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
