关于SQLServer 中行列互转的实例说明

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

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

语法：

SELECT <非透视的列>,

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

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

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

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

 AS <源查询的别名>

PIVOT(

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

FOR

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

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

) AS <透视表的别名>

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

执行显示：

       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

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

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

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

显示：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很简单的实现了列转行，对于类似的数据处理灰常灰常的实用，避免了使用case when 或者循环游标的复杂处理，大大提高了处理速度和代码整洁优雅。

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

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

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

 最后简单写一下：UNPIVOT用于将列名转为列值（即列转行）

语法：

UNPIVOT(

value_column

FOR  pivot_column

IN(<column_list>)

)

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