如何使用SQL语句将行和列进行转换？-技术开发专区

如何使用SQL语句将行和列进行转换？

作者：温度编辑：王玉圆 2012-02-17 00:05 来源：博客

【IT168 技术】本来只是想解决怎么把数据的行和列进行转换的，但最近觉得一些数据库SQL语句的操作，很久没用了，有点陌生。所以也就随笔记录一些简单但很基本的操作。

　　我的数据库是MSSQL2005.

　　第一部分主要的操作包含：数据库的创建、删除，表的增、删、改，表中数据的增、删、改、查，视图的操作。

--查询数据库是否存在
if exists ( select * from sysdatabases where [name]='TestDB')
print 'Yes, the DB exists'
else
print 'No, need a new one?'

--新建一个数据库
create database TestDB on
(
     name = 'TestData',
     filename = 'G:\DBS\KeyTest.mdf',
     size = 3,
     filegrowth = 2
)
log on
(
     name = 'TestLog',
     filename = 'G:\DBS\KeyTest.ldf',
     size = 3,
     filegrowth = 10
)

--drop database TestDB

use TestDB
go

--新建一个表
create table [Scores]
(
     [ID] int identity(1,1) primary key,
     [Student] varchar(20) ,
     [Subject] varchar(30),
     [Score] float
)

--drop table [Scores]

--修改表中的一列
alter table Scores alter column [Student] varchar(20) not null

--新增一列
alter table Scores add Birthday datetime

--删除一列
alter table Scores drop column Birthday

--往表中插入单条数据，方法1：带列名
insert into Scores(Student,Subject,Score)
values('张三','语文','90')

--往表中插入单条数据，方法2：不带列名，但要求值的类型要和列字段类型对应
insert into Scores
values('张三','英语','95')

--插入多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select '李四','语文','89'
union all
select '李四','英语','78'

--删除表中数据，没有条件时，删除所有
delete from Scores where ID in(7,8)

--修改表中数据
update Scores
set Student='王五',Score='94'
where ID=10

--查看数据
select * from Scores

--查看表中最大的identity值
select @@identity

--或者利用dbcc命令查看表中最大的identity值
dbcc checkident('Scores',noreseed)

--创建视图，全部省略视图的属性列名，由子查询目标列的字段组成
create view StudentView
as
select Student,Subject,Score
from Scores

--加上with check option，以后对视图的操作（增，改，删，查）都会自动加上where ID>3
/*
create view StudentView
as
select Student,Subject,Score
from Scores
where ID>3
with check option
*/

--创建视图，全部定义属性列名，需要定义列名的情况：
----某个目标列（子查询）不是单纯的属性列，而是聚集函数或列表达式
----多表连接时选出了几个同名列
----需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score=(select max(Score) from Scores)

--查询视图，和基本表完全样，只不过如果视图中有with check option，会自动加上那个条件
select *
from StudentView

--查询自定义列名的视图
select *
from IS_Student

--对视图的insert/delete/update，和对基本表的操作一样，并且最终都是用RDBMS自动转换为对基本表的更新
--并不是所有的视图都是可更新的，因为有些视图的更新不能有意义的转换成对相应基本表的更新

--删除视图
drop view StudentView

　　第二部分，这次练习的主题。

　　【一】行转列

　　1，查询原始的数据

/***这次练习的主题，行转列，列转行***/
select * from Scores

如何使用SQL语句将行和列进行转换？

　　2，得到姓名，通过group by

select Student as '姓名'
from Scores
group by Student
order by Student

如何使用SQL语句将行和列进行转换？

　　3，再加上max， case……when

select Student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”，就选此行作为列
max(case Subject when '英语' then Score else 0 end ) as '英语'
from Scores
group by Student
order by Student

如何使用SQL语句将行和列进行转换？

　　查看其它资料时，看到另外一种方法，用pivot

--group by, avg/max, pivot。这里用max和avg，结果都一样，有什么区别吗？有点不明白
--参考网上的资料，用法如下
/*
pivot(
　　聚合函数(要转成列值的列名)
　　for 要转换的列
　　in（目标列名）
　　)
*/
select Student as '姓名',
avg(语文) as '语文',avg(英语) as '英语'from Scorespivot(    avg(Score) for Subject     in (语文,英语)    )as NewScoresgroup by Studentorder by Student asc

关注我们