简单行列转换记录

因为做排课的时候需要用到行列转换,我们想要的是类似于这样的效果

（图一）

姓名	计算机科学	运筹学
张三	Y	N
李四	Y	N
王五	N	Y

 

 

 

 

 

 

如果把这当做一张表，它的查询结果应该是这样的

（图二）

张三	计算机科学	Y
张三	运筹学	N
李四	计算机科学	Y
李四	运筹学	N
王五	计算机科学	N
王五	运筹学	Y

 

    

        

    

   

 要想得到图一的效果，就需要用到行列转换。

1，我们先来创建几张表。

1.1创建班级表
GO
CREATE TABLE [dbo].[Class](
	[cid] [int] IDENTITY(1,1) NOT NULL,
	[classname] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[cid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


1.2创建学生表
GO
CREATE TABLE [dbo].[Student](
	[sid] [int] IDENTITY(1,1) NOT NULL,
	[username] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[userpwd] [varchar](64) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[cid] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[sid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Student]  WITH CHECK ADD  CONSTRAINT [FK_Student_Class] FOREIGN KEY([cid])
REFERENCES [dbo].[Class] ([cid])

1.3创建课程表
GO
CREATE TABLE [dbo].[Course](
	[crid] [int] IDENTITY(1,1) NOT NULL,
	[coursename] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[cid] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[crid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Class] FOREIGN KEY([cid])
REFERENCES [dbo].[Class] ([cid])

2.为这三张表创建关联视图，可知班级和学生，班级和课程都是一对多的关系。

3.向各表中插入测试数据。

INSERT INTO [dbo].[Class] ([classname]) VALUES ('一期班')

INSERT INTO [dbo].[Class] ([classname]) VALUES ('二期班')

INSERT INTO [dbo].[Class] ([classname]) VALUES ('三期班')

INSERT INTO [dbo].[Student] ([username] ,[userpwd] ,[cid]) VALUES ('张三' ,'111' ,1)

INSERT INTO [dbo].[Student] ([username] ,[userpwd] ,[cid]) VALUES ('李四' ,'111' ,1)

INSERT INTO [dbo].[Student] ([username] ,[userpwd] ,[cid]) VALUES ('小李' ,'111' ,1)

INSERT INTO [dbo].[Student] ([username] ,[userpwd] ,[cid]) VALUES ('王五' ,'111' ,2)

INSERT INTO [dbo].[Student] ([username] ,[userpwd] ,[cid]) VALUES ('马六' ,'111' ,2)

INSERT INTO [dbo].[Course] ([CourseName],[cid]) VALUES ('计算机科学',1)

INSERT INTO [dbo].[Course] ([CourseName],[cid]) VALUES ('运筹学',2)

INSERT INTO [dbo].[Course] ([CourseName],[cid]) VALUES ('逻辑学',2)

INSERT INTO [dbo].[Course] ([CourseName],[cid]) VALUES ('高级数学',1)

3.由于需要对这三张表联合查询进行操作，还是创建视图比较靠谱。

于是乎创建视图

CREATE VIEW [dbo].[vm_classCourse]
AS

CREATE VIEW [dbo].[vm_classCourse]
AS
SELECT     dbo.Class.*, dbo.Course.coursename, dbo.Course.crid, dbo.Student.sid, dbo.Student.username, dbo.Student.userpwd
FROM         dbo.Class INNER JOIN
                      dbo.Course ON dbo.Class.cid = dbo.Course.cid INNER JOIN
                      dbo.Student ON dbo.Class.cid = dbo.Student.cid

GO

　　

所得结果列表，（数据位测试数据，和建表数据有出入）

如何得到图一的效果呢？Declare @sql varchar(8000)

Set @sql = 'Select username as 姓名,userpwd as 密码'
Select @sql = @sql + ',sum(case coursename when '''+coursename+''' then sid else 0 end) ['+coursename+']'
from (select distinct coursename from vm_classcourse where cid=1) as vm_classcourse   --把所有唯一的科目的名称都列举出来
Select @sql = @sql+' from vm_classcourse group by username,userpwd,cid having cid=1'  --cid为筛选条件

Exec (@sql)

这就得到了类似于图一的结果集，一下是班级ID为1的结果　　

在这里做个记录，方便接下来使用。