- 浏览: 463434 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
chexiazi:
一样的xml代码 报这个错 <ns1:XMLFault ...
CXF 通过用户名和密码进行验证 -
di1984HIT:
谢谢啊 ~~~
通过JavaCompiler进行编译java文件(转载) -
aa00aa00:
'%${userName}%' 这种是可以的,是可以模糊查询的 ...
mybatis 模糊查询 -
一棵杨柳的地盘:
我把你的代码不了一遍 但是汇报错首先:static { ...
CXF 通过用户名和密码进行验证 -
shangmin1990:
转 IntelliJ IDEA 编辑器生成 Hibernate 实体映射文件
PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法:
table_source
PIVOT(
聚合函数(value_column)
FOR pivot_column
IN(<column_list>)
)
UNPIVOT用于将列明转为列值(即列转行),在SQL Server 2000可以用UNION来实现
完整语法:
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
注意:PIVOT、UNPIVOT是SQL Server 2005 的语法,使用需修改数据库兼容级别
在数据库属性->选项->兼容级别改为 90
典型实例
一、行转列
1、建立表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10),课程varchar(10),分数int)
insertintotbvalues('张三','语文',74)
insertintotbvalues('张三','数学',83)
insertintotbvalues('张三','物理',93)
insertintotbvalues('李四','语文',74)
insertintotbvalues('李四','数学',84)
insertintotbvalues('李四','物理',94)
go
select*fromtb
go
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
2、使用SQL Server 2000静态SQL
--c
select姓名,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理
fromtb
groupby姓名
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
3、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
--变量按sql语言顺序赋值
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'
from(selectdistinct课程fromtb)a--同from tb group by课程,默认按课程名排序
set@sql=@sql+' from tb group by姓名'
exec(@sql)
--使用isnull(),变量先确定动态部分
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'
from(selectdistinct课程fromtb)asa
set@sql='select姓名,'+@sql+' from tb group by姓名'
exec(@sql)
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
4、使用SQL Server 2005静态SQL
select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a
5、使用SQL Server 2005动态SQL
--使用stuff()
declare @sql varchar(8000)
set @sql='' --初始化变量@sql
select @sql= @sql + ','+ 课程 from tb group by 课程 --变量多值赋值
--同select @sql = @sql + ','+课程from (select distinct课程from tb)a
PRINT @sql
set @sql=stuff(@sql,1,1,'')--去掉首个','
set @sql='select m.* , n.总分,n.平均分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in ('+@sql+')) b) m ,
(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名'
exec(@sql)
--或使用isnull()
declare @sql varchar(8000)
--获得课程集合
select @sql=isnull(@sql+',','')+ 课程 from tb group by 课程
set @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'
exec(@sql)
二、行转列结果加上总分、平均分
1、使用SQL Server 2000静态SQL
--SQL SERVER 2000静态SQL
select姓名,
max(case课程when'语文'then分数else0end)语文,
max(case课程when'数学'then分数else0end)数学,
max(case课程when'物理'then分数else0end)物理,
sum(分数)总分,
cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名
姓名 语文 数学 物理 总分 平均分
---------- ----------- ----------- ----------- -----------
李四 74 84 94 252 84.00
张三 74 83 93 250 83.33
2、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'
from(selectdistinct课程fromtb)a
set@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名'
exec(@sql)
3、使用SQL Server 2005静态SQL
selectm.*,n.总分,n.平均分
from
(select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a)m,
(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名)n
wherem.姓名=n.姓名
4、使用SQL Server 2005动态SQL
--使用stuff()
--
declare@sqlvarchar(8000)
set@sql='' --初始化变量@sql
select@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值
--同select @sql = @sql + ','+课程from (select distinct课程from tb)a
set@sql=stuff(@sql,1,1,'')--去掉首个','
set@sql='select m.* , n.总分,n.平均分from
(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,
(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) n
where m.姓名= n.姓名'
exec(@sql)
--或使用isnull()
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+课程fromtbgroupby课程
set@sql='select m.* , n.总分,n.平均分from
(select * from (select * from tb) a pivot (max(分数) for课程in ('+
@sql+')) b) m ,
(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) n
where m.姓名= n.姓名'
exec(@sql)
二、列转行
1、建立表格
if object_id('tb2')is not null drop table tb2
go
create table tb2(姓名 varchar(10),语文 int,数学 int,物理 int)
insert into tb2 values('张三',74,83,93);
insert into tb2 values('李四',74,84,94)
go
select * from tb2
go
姓名 语文 数学 物理
---------- ----------- ----------- -----------
张三 74 83 93
李四 74 84 94
2、使用SQL Server 2000静态SQL
--SQL SERVER 2000静态SQL。
select*from
(
select 姓名,课程='语文',分数=语文 from tb2
union all
select 姓名,课程='数学',分数=数学 from tb2
union all
select 姓名,课程='物理',分数=物理 from tb2
) t
order by 姓名,case 课程 when'语文'then 1 when'数学'then 2 when'物理'then 3 end
姓名 课程 分数
---------- ---- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
2、使用SQL Server 2000动态SQL
--SQL SERVER 2000动态SQL。
--调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+' union all ','')+' select姓名, [课程]='
+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb'
fromsyscolumns
whereName!='姓名'andID=object_id('tb')--表名tb,不包含列名为姓名的其他列
orderbycolid
exec(@sql+' order by姓名')
go
3、使用SQL Server 2005静态SQL
--SQL SERVER 2005动态SQL
select姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t
4、使用SQL Server 2005动态SQL
--SQL SERVER 2005动态SQL
declare@sqlnvarchar(4000)
select@sql=isnull(@sql+',','')+quotename(Name)
fromsyscolumns
whereID=object_id('tb')andNamenotin('姓名')
orderbyColid
set@sql='select姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in('+@sql+'))b'
exec(@sql)
发表评论
-
SQL中使用WITH AS提高性能-使用公用表表达式(CTE)简化嵌套SQL
2012-01-18 14:57 1118SQL中使用WITH AS提高性能-使用公用表表达式 ... -
mybatis 模糊查询
2012-01-05 18:03 3249使用数据库查询数据的时候,绝对会用到like进行模糊查询的,但 ... -
ibatis 入门
2011-12-28 12:48 1244昨天学习了一下ibatis ... -
转 JTA简介
2011-12-26 17:47 1036Java Transaction API(Java事务 ... -
数据库 left join,right join,inner join用法举例
2011-11-30 16:30 1381现有A和B两个表表A记录如下: aID aNu ... -
sql Server like 匹配符的介绍
2011-04-27 14:18 2768like关键字是一个匹配预算符,它与字符串表达式相匹配,字符串 ... -
sql Server varchar和nvarchar的区别
2011-04-11 11:16 1605SQL Server中字符串的存储 ... -
sql in
2009-02-25 10:32 1045在 SQL 中,在两个情况下会用到 IN 这个指令;这一页将介 ... -
sql like
2009-02-25 10:37 934LIKE 是另一个在 WHERE 子句中会用到的指令。基本上, ... -
order by
2009-02-25 10:52 948到目前为止,我们已 ... -
count 函数
2009-02-25 10:58 928COUNT 是函数之一。由于它的使用广泛,我们在这里特别提出来 ... -
group by
2009-02-25 11:04 1022我们现在回到函数上。记得我们用 SUM 这个指令来算出所有的 ... -
having 子句
2009-02-25 11:09 893那我们如何对函数产生的值来设定条件呢?举例来说,我们可能只需要 ... -
内连接
2009-02-25 11:23 884现在我们介绍连接(join)的概念。要了解连接,我们需要用到许 ... -
外链接
2009-02-25 11:31 775之前我们看到的左连接 (left join),又称内部连接 ( ... -
subquery 框架
2009-02-25 11:39 921我们可以在一个 SQL 语句中放入另一个 SQL 语句。当我们 ... -
UNION
2009-02-25 11:56 1061UNION 指令的目的是将两个 SQL 语句的结果合并起来。从 ... -
Union all
2009-02-25 12:10 1104UNION ALL 这个指令的目的也是要将两个 SQL 语句的 ... -
sql 中Intersect
2009-02-25 12:15 1369和 UNION 指令类似, INTERSECT 也是对两个 S ... -
sql中MInus
2009-02-25 12:23 1537MINUS 指令是运用在两个 SQL 语句上。它先找出第一个 ...
相关推荐
SQL行列转换 Pivot UnPivot
动态 实现 SQL 2008行列转换的pivot
主要介绍了SQLServer行列互转实现思路,使用聚合函数pivot/unpivot实现行列互转,感兴趣的小伙伴们可以参考一下
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
SQL语句行列转换(附带数据库、表、视图操作) ,不错的文档。
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
主要介绍了sql server通过pivot对数据进行行列转换的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
此文档中详细的记载了,SQL Server 2005新功能之PIVOT的描述,希望可以帮到下载的朋友们!
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
很多书籍包括教材介绍pivot语句不详细,难以理解其查询结果,这样势必会造成使用该语句时有困难,本文理论讲解透彻,结合例题分析,最后给出实验题目让你练手,以尽快掌握其使用。
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,例如一下的表为某店铺的一周收入情况表: WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL) 我们先插入一些模拟数据: INSERT INTO WEEK_INCOME SELECT ...
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
Oracle 11g中的pivot和unpivot转换操作
t-sql编程实现静态和动态的行转列、列转行代码
T-SQL语句中,Pivot运算符用于在列和行之间对数据进行旋转或透视转换,PIVOT命令可以实现数据表的列转行,同时执行聚合运算,UNPIVOT则与其相反,实现数据的行转列。
If youre a database or data warehouse developer, this is the expert resource you need to build full-scale, multi-dimensional, database applications using Microsofts new "SQL Server 2012 Analysis ...