SQL语句行列转换（附带数据库、表、视图操作）
本来只是想解决怎么把数据的行和列进行转换的，但最近觉得一些数据库SQL语句的操作，很久没用了，有点陌生。所以也就随笔记录一些简单但很基本的操作。我的数据库是MSSQL2005.
第一部分主要的操作包含：数据库的创建、删除，表的增、删、改，表中数据的增、删、改、查，视图的操作。
1 --查询数据库是否存在
2 if exists ( select * from sysdatabases where [name]='TestDB')
3 print 'Yes, the DB exists'
4 else
5 print 'No, need a new one?'
6
7 --新建一个数据库
8 create database TestDB on
9 (
10 name = 'TestData',
11 filename = 'G:\DBS\KeyTest.mdf',
12 size = 3,
13 filegrowth = 2
14 )
15 log on
16 (
17 name = 'TestLog',
18 filename = 'G:\DBS\KeyTest.ldf',
19 size = 3,
20 filegrowth = 10
21 )
22
23 --drop database TestDB
24
25 use TestDB
26 go
27
28 --新建一个表
29 create table [Scores]
30 (
31 [ID] int identity(1,1) primary key,
32 [Student] varchar(20) ,
33 [Subject] varchar(30),
34 [Score] float
35 )
36
37 --drop table [Scores]
38
39 --修改表中的一列
40 alter table Scores alter column [Student] varchar(20) not null
41
42 --新增一列
43 alter table Scores add Birthday datetime
44
45 --删除一列
46 alter table Scores drop column Birthday
47
48 --往表中插入单条数据，方法1：带列名
49 insert into Scores(Student,Subject,Score)
50 values('张三','语文','90')
51
52 --往表中插入单条数据，方法2：不带列名，但要求值的类型要和列字段类型对应
53 insert into Scores
54 values('张三','英语','95')
55
56 --插入多条数据:用union或者union all
57 insert into Scores(Student,Subject,Score)
58 select '李四','语文','89'
59 union all
60 select '李四','英语','78'
61
62 --删除表中数据，没有条件时，删除所有
63 delete from Scores where ID in(7,8)
64
65 --修改表中数据
66 update Scores
67 set Student='王五',Score='94'
68 where ID=10
69
70 --查看数据
71 select * from Scores
72
73 --查看表中最大的identity值
74 select @@identity
75
76 --或者利用dbcc命令查看表中最大的identity值
77 dbcc checkident('Scores',noreseed)
78
79 --创建视图，全部省略视图的属性列名，由子查询目标列的字段组成
80 create view StudentView
81 as
82 select Student,Subject,Score
83 from Scores
84
85 --加上with check option，以后对视图的操作（增，改，删，查）都会自动加上where ID>3
86 /*
87 create view StudentView
88 as
89 select Student,Subject,Score
90 from Scores
91 where ID>3
92 with check option
93 */
94
95 --创建视图，全部定义属性列名，需要定义列名的情况：
96 ----某个目标列（子查询）不是单纯的属性列，而是聚集函数或列表达式
97 ----多表连接时选出了几个同名列
98 ----需要在视图中为某个列启用新的更合适的名字
99 create view IS_Student(Student,Subject,MaxScore)
100 as
101 select Student,Subject,Score
102 from Scores
103 where Score=(select max(Score) from Scores)
104
105
106 --查询视图，和基本表完全样，只不过如果视图中有with check option，会自动加上那个条件
107 select *
108 from StudentView
109
110 --查询自定义列名的视图
111 select *
112 from IS_Student
113
114 --对视图的insert/delete/update，和对基本表的操作一样，并且最终都是用RDBMS自动转换为对基本表的更新
115 --并不是所有的视图都是可更新的，因为有些视图的更新不能有意义的转换成对相应基本表的更新
116
117 --删除视图
118 drop view StudentView
第二部分，这次练习的主题。
【一】行转列
1，查询原始的数据
/***这次练习的主题，行转列，列转行***/
select * from Scores
2，得到姓名，通过group by
select Student as '姓名'
from Scores
group by Student
order by Student
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
查看其它资料时，看到另外一种方法，用pivot
--group by, avg/max, pivot。这里用max和avg，结果都一样，有什么区别吗？有点不明白
--参考网上的资料，用法如下
/*
pivot(
聚合函数(要转成列值的列名)
for 要转换的列
in（目标列名）
)
*/
select Student as '姓名',
avg(语文) as '语文',
avg(英语) as '英语'
from Scores
pivot(
avg(Score) for Subject
in (语文,英语)
)as NewScores
group by Student
order by Student asc
摘自 http://www.cnblogs.com/kboy/archive/2012/02/16/2353796.html
