简单行列转换记录

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

(图一)

姓名 计算机科学 运筹学
张三 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的结果  

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

posted @ 2014-05-15 17:17  程序新青年  阅读(122)  评论(0编辑  收藏
============================================================================== 青春匆匆,很多人都有自己的座右铭,鞭策自己前进,当没看到座右铭的时候又忘了自己要干什么,就这样天天立志,志天天立,最终还是那个初出茅庐的小菜鸟。从现在开始,慢慢去改掉懒惰的习惯。慢慢去加强学习,直到慢慢成功。==============================================================================