如何使用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
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值