在MS-SQLServer 2005 中可以使用pivot运算符来来实现行列转换.,但在之前版本中必须使用 case when then else end 语句
下面以学生成绩表来举例:
id姓名 科目 成绩
1张三语文602张三数学653张三外语704李四语文805李四数学906李四外语857王五语文708王五数学719王五外语7510赵六语文6411赵六数学6712赵六外语76
查询后得出:
姓名语文数学外语
李四80 90 85王五70 71 75张三60 65 70赵六64 67 76
准备数据:
if exists(select id from sysobjects where xtype=’U’ and)drop table studentscore–删除与实验冲突的表gocreate table studentscore–创建实验表([id] int identity(1,1),[name] nvarchar(20) not null,subject nvarchar(20) not null,score int not null)go–添加实验数据insert studentscore values (‘张三’,’语文’,’60’);insert studentscore values (‘张三’,’数学’,’65’);insert studentscore values (‘张三’,’外语’,’70’);insert studentscore values (‘李四’,’语文’,’80’);insert studentscore values (‘李四’,’数学’,’90’);insert studentscore values (‘李四’,’外语’,’85’);insert studentscore values (‘王五’,’语文’,’70’);insert studentscore values (‘王五’,’数学’,’71’);insert studentscore values (‘王五’,’外语’,’75’);insert studentscore values (‘赵六’,’语文’,’64’);insert studentscore values (‘赵六’,’数学’,’67’);insert studentscore values (‘赵六’,’外语’,’76’);goselect [id], [name], subject, score from studentscorego
1张三语文602张三数学653张三外语704李四语文805李四数学906李四外语857王五语文708王五数学719王五外语7510赵六语文6411赵六数学6712赵六外语76
先利用case when then else end 语句将行转为列:
select [name],[语文]=sum(case when subject=’语文’ then score else null end),[数学]=sum(case when subject=’数学’ then score else null end),[外语]=sum(case when subject=’外语’ then score else null end)from studentscore group by [name]
查询结果：
李四809085王五707175张三606570赵六646776
以上查询作用也很大，对于很多情况，比如产品销售表中按照季度统计、按照月份统计等列头内容固定的情况，这样就行了，但往往大多数情况下列头内容是不固定的，象City，用户随时可能删除、添加一些城市，，这种情况就是我们所说的动态交叉表，这个时候需要拼下SQL语句了。
SQLServer中局部变量赋值方法有两种:一种: set @变量名 = 值二种: select @变量名 = 值第二种可以从某个表中得到数据再赋值给变量例: 从用户信息表中查询中cid为 20 的用户姓名将他赋值给变量 namedeclare @name varchar(10) –用户名select @name=userName from userInfo where cid = 20print ‘cid为20的用户姓名:’ + @name递归的select变量：递归的select变量是指使用select语句和子查询将一个变量与其自身拼接起来。语法形式如下：select @variable = @variable + table.column from table
declare @sql varchar(max)set @sql = ‘select [name],’select @sql = @sql + ‘sum(case subject when ”’+subject+”’then score else null end) as ”’+subject+”’,’from (select distinct subject from studentscore) as aselect @sql = left(@sql,len(@sql)-1) + ‘ from studentscore group by [name]’exec(@sql)
执行结果：
李四908580王五717570张三657060赵六677664
拥有一颗比九万五千公里还辽阔的心，
