SQL 关于pivot
解释: PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式,并在必要时对最终输出中所需的任何其余的列值执行聚合。
还是通过实例来了解用法和作用:
先建表一([dbo].[Customers] 字段说明:customerid -- 消费者id , city -- 所在城市):
CREATE TABLE [dbo].[Customers]( [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NOT NULL, [city] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, PRIMARY KEY CLUSTERED ( [customerid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
向表一插入数据:
insert into dbo.Customers values('FISSA','Madrid'); insert into dbo.Customers values('FRNDO','Madrid'); insert into dbo.Customers values('KRLOS','Madrid'); insert into dbo.Customers values('MRPHS','Zion');
查询所插入的数据:
select * from dbo.Customer
结果如图:
再建表二([dbo].[Orders] 字段说明:orderid -- 订单id , customerid -- 消费者id):
CREATE TABLE [dbo].[Orders]( [orderid] [int] NOT NULL, [customerid] [char](5) COLLATE Chinese_PRC_CI_AS NULL, PRIMARY KEY CLUSTERED ( [orderid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
向表二插入数据:
insert into dbo.Orders values(1,'FRNDO'); insert into dbo.Orders values(2,'FRNDO'); insert into dbo.Orders values(3,'KRLOS'); insert into dbo.Orders values(4,'KRLOS'); insert into dbo.Orders values(5,'KRLOS'); insert into dbo.Orders values(6,'MRPHS'); insert into dbo.Orders values(7,null);
查询插入的数据:
select * from dbo.orders
结果如图:
操作:
在了解PIVOT之前先看这一个题目:
题目1:每个所在城市的消费者的订单情况(请用“没有订单”,“小于两个订单”,“超过两个订单”)Sql语句如下:
select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city
结果如图:
这个很容易理解不作详解
再引入题目2:每个城市的每种订单情况(请用“没有订单”,“小于两个订单”,“超过两个订单”)的数
方法一:用普通的sql语句进行写:
select 所在城市 , count(case when 种类 = '没有订单' then 消费者 end) as [没有订单], count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单], count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单] from ( select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city ) as d group by 所在城市
结果如图:
方法二:用pivot看它的神奇之处:
select 所在城市,没有订单,小于两个订单,超过两个订单 from ( select c.customerid as '消费者', city as '所在城市', case when count(orderid) = 0 then '没有订单' when count(orderid) <= 2 then '小于两个订单' when count(orderid) > 2 then '超过两个订单' end as '种类' from dbo.Customers as c left outer join dbo.Orders as o on c.customerid = o.customerid group by c.customerid,city ) as d pivot(count(消费者) for 种类 in (没有订单,小于两个订单,超过两个订单)) as p
结果如图:
分析(在看分析时请结合方法一,便于理解):
在看方法二的sql语句可以发现pivot把d的两列(消费者,种类 )作为输入参数,而没有作为输入参数(所在城市 )的列作了一个隐式的分组(相当于方法一最后的:group by 所在城市 ),其次它又对 in 子句作了如下操作sql操作:
case when 种类 = '没有订单' then 消费者 end, case when 种类 = '小于两个订单' then 消费者 end, case when 种类 = '超过两个订单' then 消费者 end
最后对每个case表达式作指定聚合函数sql代码如下:
count(case when 种类 = '没有订单' then 消费者 end) as [没有订单], count(case when 种类 = '小于两个订单' then 消费者 end) as [小于两个订单], count(case when 种类 = '超过两个订单' then 消费者 end) as [超过两个订单]
好了通过结合方法一与方法二 就应该能够理解pivot的用法了!
相关推荐
SQL Server 关于 Pivot 详解 在本文中,我们将详细介绍 SQL Server 中的 Pivot 语句,并结合实例分析和实验题目,帮助读者快速掌握其使用。 Pivot 语法 Pivot 语句的基本语法如下: ``` SELECT <非透视的列>, ...
SQL 行列转换 Pivot UnPivot SQL 行列转换是指将表格中的行数据转换为列数据,或者将列数据转换为行数据。Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要...
此文档中详细的记载了,SQL Server 2005新功能之PIVOT的描述,希望可以帮到下载的朋友们!
PowerPivot 与 Excel 和 SharePoint 集成。在 Excel 环境中,PowerPivot for Excel 提供熟悉的工作站式的创作和分析体验。在 SharePoint 场中,PowerPivot for SharePoint 添加了服务器端应用程序和功能,支持对您...
动态 实现 SQL 2008行列转换的pivot
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
在的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里危险的一个——很快你会知道为什么。在我们进入特定问题和陷阱前,首先...
带条件查询的参数化动态PIVOT行转列,方便快速完成行转列数据处理。
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
SQL行转列学习谢谢!
枢纽表达式PIVOT和UNPIVOT是SQL Server 2005中新增的关系运算符,用于实现行列的旋转。本篇文章将介绍PIVOT和UNPIVOT的简单用法,并提供实例代码。 一、枢纽表达式PIVOT和UNPIVOT的概念 PIVOT和UNPIVOT是SQL ...
主要给大家介绍了关于行转列之SQL SERVER PIVOT与用法的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL SERVER具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
Excel PivotTable 用法介绍 Excel PivotTable 是一种强大的数据分析工具,可以帮助用户快速地将大量数据转换为有用的信息。在本文中,我们将介绍如何创建一个 PivotTable,从创建数据源到生成最终的 PivotTable ...
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b') ``` 这个查询将生成一个新的表格,其中每一行对应一个学生,列对应不同的课程,单元格中的值对应该学生在该课程中的...
This intermediate course focuses on using SQL as a data query and manipulation tool. You learn to use the SQL procedure as a data retrieval tool within SAS programs. Specifically, you learn how to ...
T-SQL语句中,Pivot运算符用于在列和行之间对数据进行旋转或透视转换,PIVOT命令可以实现数据表的列转行,同时执行聚合运算,UNPIVOT则与其相反,实现数据的行转列。
SQL 语句行列转换(附带数据库、表、视图操作) SQL 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management ...
主要给大家介绍了关于SQL基础教程之行转列Pivot函数的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
昨天遇到一个SQL Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据 : 类别 名称 AAA 企业1 AAA 企业2 AAA 企业3 ...