SQL 行转列

1、创建 学生表

CREATE TABLE [dbo].[student](
    [Sno] [smallint] NOT NULL,
    [Sname] [nvarchar](20) NULL,
 CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED 
(
    [Sno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2、创建课程表

CREATE TABLE [dbo].[course](
    [Cno] [smallint] NOT NULL,
    [Cname] [nvarchar](50) NULL,
 CONSTRAINT [PK_course] PRIMARY KEY CLUSTERED 
(
    [Cno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

3、创建成绩表

CREATE TABLE [dbo].[results](
    [Sno] [smallint] NOT NULL,
    [Cno] [smallint] NOT NULL,
    [score] [smallint] NULL,
 CONSTRAINT [PK_results] PRIMARY KEY CLUSTERED 
(
    [Sno] ASC,
    [Cno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[results]  WITH CHECK ADD  CONSTRAINT [FK_results_course] FOREIGN KEY([Cno])
REFERENCES [dbo].[course] ([Cno])
ALTER TABLE [dbo].[results] CHECK CONSTRAINT [FK_results_course]
ALTER TABLE [dbo].[results]  WITH CHECK ADD  CONSTRAINT [FK_results_student] FOREIGN KEY([Sno])
REFERENCES [dbo].[student] ([Sno])
ALTER TABLE [dbo].[results] CHECK CONSTRAINT [FK_results_student]
GO

4、学生表数据

5、课程表数据

6、成绩表

7、创建试图 View1

Create view View1 as
select s.Sname ,c.Cname ,r.score from results r,student s,course c where
r.Sno=s.Sno and r.Cno=c.Cno

视图结果为：

8、实现行转列

declare @sql1 varchar(8000)
set @sql1 = 'select Sname as ' + '姓名'
select @sql1 = @sql1 + ' , min(case Cname when ''' +  Cname + '''
then score else 100 end) [' + Cname + ']'
from (select distinct Cname from course) as a 
set @sql1 = @sql1 + ' , cast(avg(score) as decimal(18,2)) 平均分,sum(score) 总分 from AAA group by Sname'
exec(@sql1)

执行结果

下面我们来讨论为什么能够行转列

单步跟中最后exec(@sql1)

其中 @sql1=

select Sname as 姓名 ,
min(case Cname when '化学'then score else 100 end) [化学] ,
min(case Cname when '物理'then score else 100 end) [物理] , 
min(case Cname when '英语'then score else 100 end) [英语] , 
min(case Cname when '语文'then score else 100 end) [语文] , 
cast(avg(score) as decimal(18,2)) 平均分,sum(score) 总分 from AAA group by Sname

这样你是否就能理解为什么能够把行转为列了呢
上面一段代码的关键在于

select @sql1 = @sql1 + ' , min(case Cname when ''' +  Cname + '''
then score else 100 end) [' + Cname + ']'
from (select distinct Cname from course) as a

这段代码完成了min 段查询语句的组装。

其实行转列的关键就在于使用min或max函数转列，并用group by 分组，

记住这两点相信你也能写出自己的行转列代码

注：如果使用max请将else 后面的100 改成0

好了希望小子的这段东西对刚接触行转列的配有所帮助