新葡亰496net 新葡亰496net SQL Server幼功之行数据调换为列数据

SQL Server幼功之行数据调换为列数据

使用PIVOT进行行专列

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

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

转换列,此列返回的唯一值将成为最终结果集中的字段。

  表5-6 使用UNPIVOT得到的结果集

显示: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

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

1

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

总结

0

value_column

澳门葡亰娱乐场手机版,数据行中每一行行要转换的列名。

五月

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

转换后

5

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

语法解析

  a.PIVOT首先按值列之外的列(ProductID和OrderMonth)对输入表Sales.Orders进行分组汇总,类似执行下面的语句:

语法:

聚合函数列,用于输出值列,最终输出中返回的列将对其进行分组。

NULL

执行显示:

column being aggregated

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

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

示例

5

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

示例结果

3

FOR

插入数据

2

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

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;

  SELECT ProductID,OrderMonth,SUM (Orders.SubTotal) AS SumSubTotalFROM Sales.OrdersGROUP BY ProductID,OrderMonth;

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

转换前

  b.PIVOT根据FOR Orders.OrderMonth
IN指定的值5、6、7,首先在结果集中建立名为5、6、7的列,然后从图5-3所示的中间结果中取出OrderMonth列中取出相符合的值,分别放置到5、6、7的列中。此时得到的结果集的别名为pvt(见语句中AS
pvt的指定)。结果集的内容如表5-4所示。

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

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

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

)

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

  上面的语句将按下面的步骤获得输出结果集:

PIVOT(

最后一列列名。

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

SELECT <非透视的列>,

aggregation function

  表5-3 Sales.Orders表经分组汇总后的结果

) AS <透视表的别名>

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

3

UNPIVOT(

[last pivoted column]

200.00

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

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');

  表5-4 使用FOR Orders.OrderMonth IN( [5], [6], [7]
)后得到的结果集

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

数据列列名

100.00

 

语法

  FOR pivot_column

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

select query that produces the data

七月

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

数据子表。

0

FOR 
pivot_column

[first pivoted column]

100.00

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

第二列列名。

300.00

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

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

  输出表的别名。

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

select b.Name,b.[android],b.[ios],b.[html5],b.[.net] from (select Name,Project,Score from [test1].[dbo].[student])as apivot( max(Score) for Project in ([android],[ios],[html5],[.net])) as border by b.name desc

100.00

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

语法

澳门葡亰娱乐场手机版 1

 AS <源查询的别名>

示例结果

3

IN(<column_list>)

[second pivoted column]

NULL

       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

准备工作

200

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

non-pivoted column

ProductID

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

排序规则。

1

      语法

alias for the source query

NULL

aggregation function

200.00

[first pivoted column], [second pivoted column], … [last pivoted
column]

100.00

2、输出的所有列的列名的数据类型必须一致。

  CREATE TABLE MyPvt (ProductID int, 五月int, 六月 int, 七月int); --建立MyPvt表GO  --将表5-5中所示的值插入到MyPvt表中INSERT INTO MyPvt VALUES (1,100,100,0);INSERT INTO MyPvt VALUES (2,200,200,200);INSERT INTO MyPvt VALUES (3,800,0,0);  --执行UNPIVOTSELECT ProductID, OrderMonth, SubTotalFROM MyPvt UNPIVOT (SubTotal FOR OrderMonth IN  (五月, 六月, 七月) )AS unpvt;

optional order by clause

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

[column that contains the values that will become column headers]

OrderMonth

语法解析

200.00

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

六月

表别名。

  value_column

第一列列名。

  IN ( column_list )

创建表

五月

注意事项

五月

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

2

1、如果输出列名不能在表转换列中,则不会执行任何计算。

5

聚合函数。

  table_alias

标签:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图