将表值表达式的列转变为列值,PIVOT运算符将输入表的行旋转为列

图片 4

准备工作

  PIVOT和UNPIVOT关系运算符是SQL Server
2005提供的新增功能,因此,对升级到SQL Server
2005的数据库使用PIVOT和UNPIVOT时,数据库的兼容级别必须设置为90(可以使用sp_dbcmptlevel存储过程设置兼容级别)。

这几天在做一个招标系统中审批模块,其中关于报价信息这块,用到了pivot和unpivot来实现数据的行列互转,下面简单介绍一下,实际案例,便于回忆和记录相关的条件下使用的情况。pivot
与 unpivot 函数是SQL2005新提供的2个函数,
PIVOT
通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT
与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

在TSQL中,使用Pivot和Unpivot运算符将一个关系表转换成另外一个关系表,两个命令实现的操作是“相反”的,但是,pivot之后,不能通过unpivot将数据还原。这两个运算符的操作数比较复杂,记录一下自己的总结,以后用到时,作为参考。

创建表

  在查询的FROM子句中使用PIVOT和UNPIVOT,可以对一个输入表值表达式执行某种操作,以获得另一种形式的表。PIVOT运算符将输入表的行旋转为列,并能同时对行执行聚合运算。而UNPIVOT运算符则执行与PIVOT运算符相反的操作,它将输入表的列旋转为行。

      下面我通过PIVOT
来阐述整个函数的使用:

一,Pivot用法

use [test1]gocreate table [dbo].[student]( [id] [int] identity(1,1) not null, [name] [nvarchar](50) null, [project] [nvarchar](50) null, [score] [int] null, constraint [pk_student] primary key clustered ( [id] asc)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]) on [primary]go

  在FROM子句中使用PIVOT和UNPIVOT关系运算符时的语法格式如下:

      语法

Pivot旋转的作用,是将关系表(table_source)中的列(pivot_column)的值,转换成另一个关系表(pivot_table)的列名:

插入数据

  [ FROM { <table_source> } [ ,...n ] ]<table_source> ::= { table_or_view_name [ [ AS ] table_alias ] <pivoted_table> | <unpivoted_table>}<pivoted_table> ::=table_source PIVOT <pivot_clause> table_alias<pivot_clause> ::=( aggregate_function ( value_column ) FOR pivot_column  IN ( <column_list> ))<unpivoted_table> ::=table_source UNPIVOT <unpivot_clause> table_alias<unpivot_clause> ::=( value_column FOR pivot_column IN ( <column_list> ) )<column_list> ::= column_name [ , ... ] table_source PIVOT <pivot_clause>

SELECT <非透视的列>,

table_source
pivot
(
  aggregation_function(aggregated_column)
  for pivot_column in ([pivot_column_value_list])
) as pivot_table_alias
insert into test1.dbo.student(name,project,score)values('张三','android','60'), ('张三','ios','70'), ('张三','html5','55'), ('张三','.net','100'), ('李四','android','60'), ('李四','ios','75'), ('李四','html5','90'), ('李四','.net','100');

  指定对table_source表中的pivot_column列进行透视。table_source可以是一个表、表表达式或子查询。

    [第一个透视的列] AS <列名称>,

透视操作的处理流程是:

使用Case When和聚合函数进行行专列

  aggregate_function

     [第二个透视的列] AS <列名称>, …

  1. 对pivot_column和 aggregated_column的其余column进行分组,即,group
    by other_columns;
  2. 当pivot_column值等于某一个指定值,计算aggregated_column的聚合值;

语法

  系统或用户定义的聚合函数。注意:不允许使用COUNT(*)系统聚合函数。

[最后一个透视的列] AS <列名称>,

在使用透视命令时,需要注意:

select column_name,aggregation function(case when expression) from database.schema.tablegroup by column_name

  value_column

FROM(<生成数据的 SELECT 查询>)

  • pivot将table_source旋转成透视表(pivot_table)之后,不能再被引用
  • pivot_column的列值,必须使用中括号([])界定符
  • 必须显式命名pivot_table的别名

语法解析

  PIVOT运算符用于进行计算的值列。与UNPIVOT一起使用时,value_column不能是输入table_source中的现有列的名称。

 AS <源查询的别名>

1,创建示例数据

column_name

  FOR pivot_column

PIVOT(

图片 1图片 2

数据列列名

  PIVOT运算符的透视列。pivot_column必须是可隐式或显式转换为nvarchar()的类型。

<聚合函数>(<要聚合的列>)

use tempdb
go 

drop table if exists dbo.usr
go

create table dbo.usr
(
    name varchar(10),
    score int,
    class varchar(8)
)
go

insert into dbo.usr
values('a',20,'math'),('b',21,'math'),('c',22,'phy'),('d',23,'phy')
,('a',22,'phy'),('b',23,'phy'),('c',24,'math'),('d',25,'math')
go

aggregation function

  使用UNPIVOT时,pivot_column是从table_source中提取输出的列名称,table_source中不能有该名称的现有列。

FOR

View Code

聚合函数,常见的有:sum,max,min,avg,count等。

  IN ( column_list )

[<包含要成为列标题的值的列>]

图片 3

case when expression

  在PIVOT子句中,column_list列出pivot_column中将成为输出表的列名的值。

IN ( [第一个透视的列], [第二个透视的列], … [最后一个透视的列])

2,对name进行分组,对score进行聚合,将class列的值转换为列名

case when表达式

  在UNPIVOT子句中,column_list列出table_source中将被提取到单个pivot_column中的所有列名。

) AS <透视表的别名>

select p.name,p.math,p.phy
from dbo.usr u
pivot
(
    sum(score)
    for class in([math],[phy]) 
) as p

示例

  table_alias

实例:
select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from
 Q2B_QUOT_ITEM where <相关条件筛选>

图片 4

select name,max(case project when 'android' then score end) as '安卓',max(case project when 'ios' then score end) as '苹果',max(case project when 'html5' then score end) as 'html5',max(case project when '.net' then score end) as '.net'from [test1].[dbo].[student]group by name

  输出表的别名。

执行显示:

3,pivot的等价写法:使用case when语句实现

示例结果

  UNPIVOT < unpivot_clause >

       PRICE Sup_Name QUOT_ITEM1   QUOT_ITEM3 QUANTITY
         342 测试供应商1 5H52921000088 剪刀              4.000
         422 测试供应商1 5H48911000023 黑色水笔芯     2.000
         211 测试供应商1 5H57161000002 计算器           2.000
         324 测试供应商2 5H52921000088 剪刀              4.000
         342 测试供应商2 5H48911000023 黑色水笔芯      2.000
         234 测试供应商2 5H57161000002 计算器           2.000
         434 测试供应商3 5H52921000088 剪刀              4.000
         232 测试供应商3 5H48911000023 黑色水笔芯     2.000
        2432 测试供应商3 5H57161000002 计算器          2.000

pivot命令的执行流程很简单,使用caseh when子句实现pivot的功能

转换前

  指定将输入表中由column_list指定的多个列的值缩减为名为pivot_column的单个列。

发现正常情况下读取数据显示的是按照Sup_Name(供应商)作为列值显示。目前客户要求以物资为条件对各供应上报价进行汇总显示。接下来我们按照刚才提供的语法使用pivot来实现列转行。

select u.name,
    sum(case when u.class='math' then u.score else null end) as math,
    sum(case when u.class='phy' then u.score else null end) as phy
from dbo.usr u
group by u.name

转换后

  常见的可能会用到PIVOT的情形是:需要生成交叉表格报表以汇总数据。交叉表是使用较为广泛的一种表格式,例如,图5-4所示的产品销售表就是一个典型的交叉表,其中的月份和产品种类都可以继续添加。但是,这种格式在进行数据表存储的时候却并不容易管理,要存储图5-4这样的表格数据,数据表通常需要设计为图5-5这样的结构。这样就带来一个问题,用户既希望数据容易管理,又希望能够生成一种能够容易阅读的表格数据。好在PIVOT为这种转换提供了便利。

  sql:select* from
(select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from
Q2B_QUOT_ITEM where <相关条件筛选> as <别名>

使用group by子句对name列分组,使用 case when
语句将pivot_column的列值作为列名返回,并对aggregated_column计算聚合值。

使用PIVOT进行行专列

图片 5

pivot (max(PRICE) for ord.Sup_Name in
(测试供应商1,测试供应商3,测试供应商2)) b

4,动态Pivot写法

PIVOT通过将表达式中一列中的唯一值转换为输出中的多个列来旋转表值表达式。并PIVOT在最终输出中需要的任何剩余列值上运行聚合,PIVOT提供比一系列复杂的SELECT…CASE语句指定的语法更为简单和可读的语法,PIVOT执行聚合并将可能的多行合并到输出中的单个行中。

  图5-4 产品销售表 图5-5 数据表结构

显示:QUOT_ITEM1 QUOT_ITEM3 QUANTITY 测试供应商1 测试供应商3 测试供应商2
      5H48911000023 黑色水笔芯     2.000          422         
232          342
      5H52921000088 剪刀              4.000          342         
434          324
       5H57161000002 计算器          2.000          211        
2432          234

静态pivot写法的弊端是:如果pivot_column的列值发生变化,静态pivot不能对新增的列值进行透视,变通方法是使用动态sql,拼接列值

语法

  假设Sales.Orders表中包含有ProductID(产品ID)、OrderMonth(销售月份)和SubTotal(销售额)列,并存储有如表5-2所示的内容。

 使用pivot很简单的实现了列转行,对于类似的数据处理灰常灰常的实用,避免了使用case
when 或者循环游标的复杂处理,大大提高了处理速度和代码整洁优雅。

Script1,使用case-when子句实现

select non-pivoted column, [first pivoted column] as column name, [second pivoted column] as column name, ... [last pivoted column] as column name from (select query that produces the data) as alias for the source query pivot ( aggregation function(column being aggregated) for [column that contains the values that will become column headers] in ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) as alias for the pivot table optional order by clause;

  表5-2 Sales.Orders表中的内容

注意事项:1.对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和
UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高;

图片 6图片 7

语法解析

ProductID

                2.UNPIVOT 将与 PIVOT
执行几乎完全相反的操作,将列转换为行,但是也不是完全的相同,PIVOT
会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT
不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT
的输入中的空值不会显示在输出中,而在执行 PIVOT
操作之前,输入中可能有原始的空值。

declare @sql nvarchar(max)
declare @columnlist nvarchar(max)

set @columnlist=N''

;with cte as
(
select distinct class
from dbo.usr
)
select @columnlist+='sum(case when u.class='''+cast(class as varchar(10))+N''' then u.score else null end) as ['+cast(class as varchar(10))+N'],'
from cte

select @columnlist=SUBSTRING(@columnlist,1,len(@columnlist)-1)

select @sql=
N'select u.name,'
    +@columnlist
+N'from dbo.usr u
group by u.name'

exec(@sql)

non-pivoted column

OrderMonth

              
3.动态处理和静态处理不一样的地方在于列转行的数量。也就是FOR … in
包含的数据。

View Code

非聚合列。

SubTotal

 最后简单写一下:UNPIVOT用于将列名转为列值(即列转行)

Script2,使用pivot子句实现

[first pivoted column]

1

语法:

图片 8图片 9

第一列列名。

5

UNPIVOT(

declare @sql nvarchar(max)
declare @classlist nvarchar(max)

set @classlist=N''

;with cte as
(
    select distinct class
    from dbo.usr
)
select @classlist+=N'['+cast(class as varchar(11))+N'],'
from cte

select     @classlist=SUBSTRING(@classlist,1,len(@classlist)-1)

select @sql=N'select p.name,'+@classlist+
N' from dbo.usr u
PIVOT
(
    sum(score) 
    for class in('+@classlist+N')
) as p'

exec (@sql)

[second pivoted column]

100.00

value_column

View Code

第二列列名。

1

FOR 
pivot_column

二,Unpivot用法

[last pivoted column]

6

IN(<column_list>)

unpivot是将列名转换为列值,列名做为列值,因此,会新增两个column:一个column用于存储列名,一个column用于存储列值

最后一列列名。

100.00

)

table_soucr
unpivot
(
newcolumn_store_unpivotcolumn_name for 
newcolumn_store_unpivotcolumn_value in (unpivotcolumn_name_list)  
)

select query that produces the data

2

简单测试了一下,待以后有实际应用再把实际例子复制过来。不继续深入探讨了。

逆透视(unpivot)的处理流程是:

数据子表。

5

 

  1. unpivotcolumn_name_list是逆透视列的列表,其列值是相兼容的,能够存储在一个column中
  2. 保持其他列(除unpivotcolumn_name_list之外的所有列)的列值不变
  3. 依次将unpivotcolumn的列名存储到newcolumn_store_unpivotcolumn_name字段中,将unpivotcolumn的列值存储到newcolumn_store_unpivotcolumn_value字段中

alias for the source query

200.00

1,创建示例数据

表别名。

2

图片 10图片 11

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图