SQL语句行列转换

SQL语句行列转换（附带数据库、表、视图操作）

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

我的数据库是MSSQL2005.

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

1--查询数据库是否存在

2if exists ( select*from sysdatabases where[name]='TestDB')

3print'Yes, the DB exists'

4else

5print'No, need a new one?'

6

7--新建一个数据库

8create database TestDB on

9 (

10 name ='TestData',

11 filename ='G:\DBS\KeyTest.mdf',

12 size =3,

13 filegrowth =2

14 )

15log on

16 (

17 name ='TestLog',

18 filename ='G:\DBS\KeyTest.ldf',

19 size =3,

20 filegrowth =10

21 )

22

23--drop database TestDB

24

25use TestDB

26go

27

28--新建一个表

29create 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--修改表中的一列

40alter table Scores alter column[Student]varchar(20) not null 41

42--新增一列

43alter table Scores add Birthday datetime

44

45--删除一列

46alter table Scores drop column Birthday

47

48--往表中插入单条数据，方法1：带列名

49insert into Scores(Student,Subject,Score)

50values('张三','语文','90')

51

52--往表中插入单条数据，方法2：不带列名，但要求值的类型要和列字段类型对应 53insert into Scores

54values('张三','英语','95')

55

56--插入多条数据:用union或者union all

57insert into Scores(Student,Subject,Score)

58select'李四','语文','89'

59union all

60select'李四','英语','78'

61

62--删除表中数据，没有条件时，删除所有

63delete from Scores where ID in(7,8)

64

65--修改表中数据

66update Scores

67set Student='王五',Score='94'

68where ID=10

69

70--查看数据

71select*from Scores

72

73--查看表中最大的identity值

74select@@identity

75

76--或者利用dbcc命令查看表中最大的identity值

77dbcc checkident('Scores',noreseed)

79--创建视图，全部省略视图的属性列名，由子查询目标列的字段组成

80create view StudentView

81as

82select Student,Subject,Score

83from Scores

84

85--加上with check option，以后对视图的操作（增，改，删，查）都会自动加上whe re ID>3

86/*

87create view StudentView

88as

89select Student,Subject,Score

90from Scores

91where ID>3

92with check option

93*/

94

95--创建视图，全部定义属性列名，需要定义列名的情况：

96----某个目标列（子查询）不是单纯的属性列，而是聚集函数或列表达式

97----多表连接时选出了几个同名列

98----需要在视图中为某个列启用新的更合适的名字

99create view IS_Student(Student,Subject,MaxScore)

100as

101select Student,Subject,Score

102from Scores

103where Score=(select max(Score) from Scores)

104

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

107select*

108from StudentView

109

110--查询自定义列名的视图

111select*

112from IS_Student

113

114--对视图的insert/delete/update，和对基本表的操作一样，并且最终都是用RDBM S自动转换为对基本表的更新

115--并不是所有的视图都是可更新的，因为有些视图的更新不能有意义的转换成对相应基本表的更新

116

117--删除视图

118drop 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 else0end) as'语文' ,--如果这个行是“语文”，就选此行作为列

max(case Subject when'英语'then Score else0end ) 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

复制代码