精华内容
下载资源
问答
  • 3、转换-逆透视表 (Unpivot Table) 逆透视表节点是将多维转为一维的一种数据处理操作。 举例:如下图同一用户有数学、语文、英语三类不同科目成绩,现需通过数据处理对各科成绩整合到单列中形成新数据集,便于...

    现在的报告样式多种多样,越来越炫酷以至于让人应接不暇。如果想从数据结果上进行溯源,由于数据处理过程盘根错节且技术性过强,让业务人员捉襟见肘。如何让业务人员清晰可视化的看到数据从来源、加工到展示的一步步操作,永洪科技为您排忧解难。

    今天这篇文章,主要介绍永洪自服务数据查询,这种方式提供强大、便捷的数据准备和整合方式,用户可以通过在图形化界面上只需要进行简单的拖拽和可视化的操作,便可以构建复杂的数据集。

    数据准备过程主要涉及三类功能节点:输入节点(用于实现数据库表连接、导入EXCEL数据、创建内嵌数据)、中间节点 (对输入节点进行关联和数据操作的节点)及查询结果节点。

    一、输入节点

    输入节点即数据源入口节点。可以通过连接数据库表、导入Excel、创建内嵌数据方式,将来自不同类型的查询数据作为自服务数据操作的输入节点。

    在这里插入图片描述

    二、关联&转换节点

    操作功能区中系统内嵌了关联(联接、联合)和转换(逆透视表、分组和汇总、自循环列、镜像、去重)两类数据处理节点。

    在这里插入图片描述
    1、关联节点-联接 (Join)

    通过联接节点将数据表与表之间按既定连接类型进行关联、数据整合。如果数据库表之间定义了外联接信息,联接节点会基于这些外联接信息将缺省的联接做好。

    在这里插入图片描述
    2、关联节点-联合 (Union All)

    通过连线的方式,联合节点可以将列数相同的表的数据 (包括重复数据行)拼接在一起。

    在这里插入图片描述
    在这里插入图片描述
    举例:A表包括1日到20日的交易信息,B表包括21日到30日的交易信息,现通过联合节点将两表数据整合到一起,从而能够查看到1日到30日的全周期交易信息。

    3、转换-逆透视表 (Unpivot Table)

    逆透视表节点是将多维转为一维的一种数据处理操作。

    在这里插入图片描述
    举例:如下图同一用户有数学、语文、英语三类不同科目成绩,现需通过数据处理对各科成绩整合到单列中形成新数据集,便于进行每位同学的汇总成绩分析。

    在逆透视节点中,设置数据保持不变列为保留列和需要从多维转一维的列为转换列。
    即可应用该数据集按班级、人员进行各科成绩的汇总分析,转换结果如下图所示。
    在这里插入图片描述

    4、分组和汇总 ( Aggregate)

    分组和汇总即对数据进行分组聚合。
    在这里插入图片描述
    举例:在上例的基础上,现对每位同学的成绩进行分组,并汇总每位同学的总成绩。

    5、自循环列 ( Autoloop)
    自循环列就是根据设置自动分出层级关系的列,每个查询只能创建一个自循环列。
    在这里插入图片描述
    通常我们会遇到一列中数据存在层级关系,如河北省、石家庄、正定县,三个区域存在所属关系,在区域ID上也存在父子级关系。通过自循环列操作可实现各个区域的层级划分。

    通过自循环节点进行层级处理后结果如下图所示:

    在这里插入图片描述
    6、镜像

    通过连线镜像节点,任意节点可以被复制一个或多个,在需要对同一输入节点并行进行多种数据处理的场景下应用。

    在这里插入图片描述

    7、去重
    通过连线的方式,去重节点可以把连线节点的重复记录去掉。

    举例:A表中用户1和用户数据2存在重复数据,现对其进行去重,原数据如下。

    在这里插入图片描述
    连接去重节点之后重复记录被去掉。

    在这里插入图片描述
    三、查询结果节点

    查询结果节点是所有节点数据处理的终结点,查询结果节点只能有一个输入。可通过操作查询结果节点将自服务数据集导入数据库。
    在这里插入图片描述

    四、实时性能检测

    自服务数据准备中,数据执行的绝对快慢很难得知,可以通过连线的颜色进行实时性能检测,连线颜色为绿色时表示性能快,连线为黄色时表示性能慢,用户可以根据性能快慢进行性能调优。

    如图则性能一般,需要进一步调优。在调整后可以点击画布左上角性能检测,小球会沿着输入节点的轨迹,开始滚动,在查询结果节点停止,小球的颜色跟连线的颜色相同反应性能。

    在这里插入图片描述
    五、自动布局

    自动布局功能为用户提供了节点和节点之间根据既定算法优化布局,让布局更加合理美观。

    自动布局前:
    在这里插入图片描述

    自动布局后:
    在这里插入图片描述

    总结

    在自服务查询中,用户通过可视化的工作流方式,直接利用已建好的查询对数据进行轻度建模与转换,同时也可以将外部多种数据源直接引入进行数据建模及转换。自服务查询不仅仅提供对数据进行分组与汇总、逆透视表转化等转变数据结构的功能,同时新增加多种数据转换功能如:值映射、去除空格、去除重复记录、缺失值替换、拆分列为多列、创建组等。

    自服务查询可以使得用户操作过程更流畅,全视图化界面方便用户对数据结构及细节进行查看了解。通过连线颜色及提示信息,实时友好的展示各数据节点的性能指标,并将展示数据模型的整体性能状态及时通告给用户。

    展开全文
  • 交叉报表sql语句使用

    千次阅读 2008-04-10 13:33:00
    ,并且您需要将列标识符 Emp1 、 Emp2 、 Emp3 、 Emp4 和 Emp5 转换为对应于某个特定供应商的行值。这意味着必须标识另外两个列。包含所转换列值( Emp1 、 Emp2 ...)的列将被称为 Employee ,保存当前驻留在...

     

    PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单、更具可读性。

    常见的可能会用到 PIVOT 的情形是,需要生成交叉表格报表以汇总数据时。例如,假设需要在 AdventureWorks 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商划分):

     
    USE AdventureWorks;
    GO
    SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS
    Emp4, [233] AS Emp5
    FROM
    (SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader) p
    PIVOT
    (
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY VendorID

    以下为部分结果集:

     
    VendorID    Emp1        Emp2        Emp3        Emp4   Emp5
    1 4 3 5 4 4
    2 4 1 5 5 5
    3 4 3 5 4 4
    4 4 2 5 5 4
    5 5 1 5 5 5

    将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。

     
    SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM PurchaseOrderHeader

    这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。结果,在透视子句中指定的每个 EmployeeID 号(在本例中为雇员 164、198、223、231 和 233)都有相应的一列。PurchaseOrderID 列用作值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合了分组列。请注意,将显示出一条警告消息,指明针对每个雇员计算 COUNT 时不考虑显示在 PurchaseOrderID 列中的任何 NULL 值。

    重要事项:
    如果 PIVOT 中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何 NULL 值。

     

     

    UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1Emp2Emp3Emp4Emp5 转换为对应于某个特定供应商的行值。这意味着必须标识另外两个列。包含所转换列值(Emp1Emp2...)的列将被称为 Employee,保存当前驻留在所转换列下的值的列将被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。该查询如下所示:

     
    --Create the table and insert values as portrayed in the above example.
    CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int)
    GO
    INSERT INTO pvt VALUES (1,4,3,5,4,4)
    INSERT INTO pvt VALUES (2,4,1,5,5,5)
    INSERT INTO pvt VALUES (3,4,3,5,4,4)
    INSERT INTO pvt VALUES (4,4,2,5,5,4)
    INSERT INTO pvt VALUES (5,5,1,5,5,5)
    GO
    --Unpivot the table. SELECT VendorID, Employee, Orders
    FROM
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM pvt) p
    UNPIVOT
    (Orders FOR Employee IN
    (Emp1, Emp2, Emp3, Emp4, Emp5)
    )AS unpvt
    GO

    以下为部分结果集:

     
    VendorID   Employee   Orders
    1 Emp1 4
    1 Emp2 3
    1 Emp3 5
    1 Emp4 4
    1 Emp5 4
    2 Emp1 4
    2 Emp2 1
    2 Emp3 5
    2 Emp4 5
    2 Emp5 5
    ...

    请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的 NULL 不会显示在输出中,然而在执行 PIVOT 操作之前输入中可能会含有原始的 NULL 值。

    AdventureWorks 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要对该视图编写脚本,请在 SQL Server Management Studio 对象资源管理器中 AdventureWorks 数据库对应的 Views 文件夹下找到它。然后右键单击该视图名称并选择“编写视图脚本为”。

    展开全文
  • Oracle PIVOT和UNPIVOT

    2020-09-17 22:58:32
    您可以使用Oracle PIVOT功能(相反,使用Oracle UNPIVOT)进行此操作。 目录 本指南方涵盖以下主题。您可以单击以下任一条目进入本页的该部分: 问题 Oracle SQL中的PIVOT关键字 简单的PIVOT示例 指定分组的...

    您是否曾经需要将一组数据从行转换为列?您可以使用Oracle PIVOT功能(相反,使用Oracle UNPIVOT)进行此操作。

    目录

    本指南方涵盖以下主题。您可以单击以下任一条目进入本页的该部分:

    • 问题
    • Oracle SQL中的PIVOT关键字
    • 简单的PIVOT示例
    • 指定分组的列
    • 在PIVOT中使用WHERE子句
    • 别名PIVOT列
    • 执行多个聚合
    • 按多列分组
    • XML的PIVOT示例
    • 动态指定列
    • 使用UNPIVOT将列转换为行
    • 一个简单的UNPIVOT示例
    • 在UNPIVOT中处理NULL值
    • 别名和UNPIVOT
    • 您可以不使用Oracle PIVOT进行汇总吗?

    现在,让我们进入指南!

     

    问题

     

    假设您在名为cust_sales的表中拥有这组数据:

    位置

    顾客ID

    sale_amount

    北方

    6

    875

    南方

    2

    378

    东方

    5

    136

    西方

    5

    236

    中央

    3

    174

    北方

    1

    729

    东方

    2

    147

    西方

    3

    200

    北方

    6

    987

    中央

    4

    584

    南方

    3

    714

    东方

    1

    192

    西方

    3

    946

    东方

    4

    649

    南方

    2

    503

    北方

    5

    399

    中央

    6

    259

    东方

    3

    407

    西方

    1

    545

    这代表了不同地区不同客户的一组销售。如果要查找每个位置和每个客户的销售总和怎么办?您可以使用SUMGROUP BY编写SQL查询:

    SELECT location, customer_id, SUM(sale_amount)

    FROM cust_sales

    GROUP BY location, customer_id

    ORDER BY location, customer_id;

    这将显示以下结果:

    位置

    顾客ID

    SUM(SALE_AMOUNT)

    中央

    3

    174

    中央

    4

    584

    中央

    6

    259

    东方

    1

    192

    东方

    2

    147

    东方

    3

    407

    东方

    4

    649

    东方

    5

    136

    北方

    1

    729

    北方

    5

    399

    北方

    6

    1862

    南方

    2

    881

    南方

    3

    714

    西方

    1

    545

    西方

    3

    1146

    西方

    5

    236

    如果您不想在位置和customer_id中显示两列,而是在每一行中显示位置,并在每一列中显示customer_id,该怎么办?您将寻找这样的结果:

    位置

    1

    2

    3

    4

    5

    6

    中央

    0

    0

    174

    584

    0

    259

    东方

    192

    147

    407

    649

    136

    0

    北方

    729

    0

    0

    0

    399

    1862

    南方

    0

    881

    714

    0

    0

    0

    这通常称为枢转,或转置行和列,或转置列和行。可以用SQL中的几种方法完成,最简单的方法是使用Oracle PIVOT关键字。

    窗体底端

     

    Oracle SQL中的PIVOT关键字

     

    Oracle可以创建结果集,该结果集可以转置或旋转列和行以提供摘要。这是使用SQL PIVOT关键字完成的。此关键字是在Oracle 11g中引入的。

    此关键字应用于SELECT语句,如下所示:

    SELECT columns

    FROM tables

    PIVOT [XML] (

      pivot_clause,

      pivot_for_clause,

      pivot_in_clause

    );

    PIVOT关键字之后,它包含几个组件:

    • XML:这是一个可选关键字,它使您可以XML格式输出数据。下面有一个例子。
    • pivot_clause:这定义查询将在其上聚合数据的内容,因为PIVOT关键字聚合数据。
    • pivot_for_clause:这定义了将对哪些列进行分组和透视
    • pivot_in_clause:用于过滤pivot_for_clause中各列的值。此子句中的每个值将是一个单独的列。

    如果这让您感到困惑,请放心。下面的示例将使其更容易理解。

     

    简单的PIVOT示例

     

    假设您想显示前面提到的结果:第一列中的位置,每个客户都有不同的列,并且sale_amountSUM作为值。

    在没有透视的标准GROUP BY中显示此数据的查询是:

    SELECT location,

    customer_id,

    SUM(sale_amount)

    FROM cust_sales

    GROUP BY location, customer_id

    ORDER BY location, customer_id;

    位置

    顾客ID

    SUM(SALE_AMOUNT)

    中央

    3

    174

    中央

    4

    584

    中央

    6

    259

    东方

    1

    192

    东方

    2

    147

    东方

    3

    407

    东方

    4

    649

    东方

    5

    136

    北方

    1

    729

    北方

    5

    399

    北方

    6

    1862

    南方

    2

    881

    南方

    3

    714

    西方

    1

    545

    西方

    3

    1146

    西方

    5

    236

    将其转换为透视结果集的查询如下所示:

    SELECT *

    FROM cust_sales

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4, 5, 6)

    );

    让我们分解一下这个查询:

    • 选择*。我们从SELECT * FROM cust_sales开始。通常我不喜欢使用SELECT *,但是如果我们单独列出各列,则将显示错误,因为应该由PIVOT子句确定显示的内容。
    • PIVOT:这表明我们要使用SQL数据透视功能来显示输出。我们打开括号以包含一系列参数。
    • SUMsale_amount:这是要在输出中间显示的值。这是一个应用了聚合函数的数值。
    • FOR customer_id:这是表中的列,用于在输出中显示不同的列。这些值显示为列标题。
    • IN123456:这些是上述用于输出的FOR列的值。就像WHERE过滤器一样。您不必指定所有列,但是我在这里。

    FOR子句中未提及的任何列均显示为行并进行分组。在这种情况下,将使用位置字段。

    该查询的输出为:

    位置

    1

    2

    3

    4

    5

    6

    西方

    545

    (空值)

    1146

    (空值)

    236

    (空值)

    中央

    (空值)

    (空值)

    174

    584

    (空值)

    259

    北方

    729

    (空值)

    (空值)

    (空值)

    399

    1862

    南方

    (空值)

    881

    714

    (空值)

    (空值)

    (空值)

    东方

    192

    147

    407

    649

    136

    (空值)

    这是PIVOT关键字的结果。行是不同的位置,列是customer_id 16,值是sale_amount的总和。任何NULL值都是因为不存在数据。例如,西部位置没有customer_id 2的销售。

     

    指定分组的列

     

    使用PIVOT关键字时,FOR子句中未提及的任何列都将用作Oracle PIVOT GROUP BY的一部分。在上面的示例中,唯一的一列是location列,这是可以的。

    但是,如果您的输出包含另一个称为prod_category的列怎么办?

    位置

    prod_category

    顾客ID

    sale_amount

    北方

    家具类

    2

    875

    南方

    电子产品

    2

    378

    东方

    园艺

    4

    136

    西方

    电子产品

    3

    236

    中央

    家具类

    3

    174

    北方

    电子产品

    1

    729

    东方

    园艺

    2

    147

    西方

    电子产品

    3

    200

    北方

    家具类

    4

    987

    中央

    园艺

    4

    584

    南方

    电子产品

    3

    714

    东方

    家具类

    1

    192

    西方

    园艺

    3

    946

    东方

    电子产品

    4

    649

    南方

    家具类

    2

    503

    北方

    电子产品

    1

    399

    中央

    园艺

    3

    259

    东方

    电子产品

    3

    407

    西方

    家具类

    1

    545

    如果您运行相同的PIVOT查询,则会得到此结果。在此示例中,我使用了另一个表cust_sales_category并减少了客户数量。

    SELECT *

    FROM cust_sales_category

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    结果是:

    位置

    prod_category

    1

    2

    3

    4

    西方

    园艺

    (空值)

    (空值)

    946

    (空值)

    西方

    家具类

    545

    (空值)

    (空值)

    (空值)

    东方

    电子产品

    (空值)

    (空值)

    407

    649

    中央

    家具类

    (空值)

    (空值)

    174

    (空值)

    北方

    家具类

    (空值)

    875

    (空值)

    987

    东方

    家具类

    192

    (空值)

    (空值)

    (空值)

    南方

    家具类

    (空值)

    503

    (空值)

    (空值)

    东方

    园艺

    (空值)

    147

    (空值)

    136

    北方

    电子产品

    1128

    (空值)

    (空值)

    (空值)

    西方

    电子产品

    (空值)

    (空值)

    436

    (空值)

    中央

    园艺

    (空值)

    (空值)

    259

    584

    南方

    电子产品

    (空值)

    378

    714

    (空值)

    我们的数据按位置和prod_category分组。发生这种情况是因为在FOR子句中提到了customer_id,并且该语句使用了GROUP BY的所有其他列。

    如果我们不想按位置和prod_category分组怎么办?

    我们可以尝试将SELECT查询更改为仅选择location列。

    SELECT location, customer_id, sale_amount

    FROM cust_sales_category

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    该查询的结果是:

    ORA-00904“ SALE_AMOUNT”:无效的标识符

    00904. 00000-“s:无效的标识符

    *原因:

    *行动:

    行错误:87列:31

    由于无法将各个列指定为SELECT子句的一部分,因此出现此错误。但是,有两种方法可以执行此操作:WITH子句或子查询。

    要指定作为PIVOT查询的一部分进行分组的列,可以将Oracle PIVOT与子查询一起使用,其中子查询仅显示所需的列:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    注意,如何从子查询中执行SELECT *,而不是直接从表中进行选择,子查询提到了各个列。这意味着prod_category被忽略,并且您得到如下结果:

    位置

    1

    2

    3

    4

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    结果根本没有将prod_category列分组。如果要按prod_category而不是位置进行分组,只需更改子查询中的字段即可:

    SELECT *

    FROM (

      SELECT prod_category, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    位置

    1

    2

    3

    4

    家具类

    737

    1378

    174

    987

    电子产品

    1128

    378

    1557

    649

    园艺

    (空值)

    147

    1205

    720

    这样便可以通过使用PIVOT查询来更改要显示和分组的列:通过将Oracle PIVOT与子查询一起使用。

     

    PIVOT中使用WHERE子句

     

    在上面的查询中,结果显示了所有数据的关键摘要。所有记录均按几个字段分组,并显示销售金额的总和。

    如果您想将其限制为仅某些行怎么办?

    您可以使用WHERE子句,就像普通的SELECT查询一样。

    但是,如果将WHERE子句放在中间,则会出现错误:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    WHERE location <> 'south'

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    ORA-00933SQL命令未正确结束

    00933.00000-“ SQL命令未正确结束

    *原因:

    *行动:

    行错误:127列:1

    这是因为PIVOT子句必须位于WHERE子句之后。正确的查询如下所示:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    )

    WHERE location <> 'south';

    WHERE子句在查询的末尾,在SQL PIVOT子句之后。显示以下结果:

    位置

    1

    2

    3

    4

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    东方

    192

    147

    407

    785

    结果不包括位置等于南方的记录。

    如果使用子查询方法确定列,则还可以将WHERE子句放在子查询中:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

      WHERE location <> 'south'

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    您将获得相同的结果。

     

    别名PIVOT

     

    到目前为止,我们查看的查询将列标题显示为存储在表中的customer_id值。如果您想给他们一个不同的名字怎么办?PIVOT关键字允许您指定列别名。这可以在pivot_clausepivot_in_clause上完成。

    此示例为SUM值提供别名:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    您可以看到我们已经使用AS sales_totalSUMsale_amount)别名。该查询的结果是:

    位置

    1_SALES_TOTAL

    2_SALES_TOTAL

    3_SALES_TOTAL

    4_SALES_TOTAL

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    Oracle PIVOT列名称现在显示为1_SALES_TOTAL2_SALES_TOTAL,依此类推。这由<customer_id> _ <alias_name>确定。1_SALES_TOTAL的第一列是customer_id 1sales_total值。

    相反,您可以为IN子句中的列值加上别名:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4)

    );

    位置

    客户1

    客户2

    客户3

    客户4

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    列值正好显示您对它们的别名。它没有显示1,而是显示CUST1

    最后,您可以结合使用pivot_clause别名和pivot_in_clause别名:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (1 AS cust1, 2 AS cust2, 3 AS cust3, 4 AS cust4)

    );

    位置

    CUST1_SALES_TOTAL

    CUST2_SALES_TOTAL

    CUST3_SALES_TOTAL

    CUST4_SALES_TOTAL

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    这已级联无论从pivot_in_clausecust1)别名并从pivot_clausesales_total)别名,通过下划线将它们分离:cust1_sales_total

     

    执行多个聚合

     

    在到目前为止的示例中,我们在单个列上执行了一次聚合:对不同的customer_id组合进行了SUM运算。如果需要,我们可以在SQL PIVOT查询中扩展它,以执行更多操作。

    我们可以在我们的PIVOT查询中添加第二个聚合函数。例如,假设要显示SUM,我们还要显示每个组中的COUNT个记录。查询如下所示:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount),

      COUNT(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    如果运行此查询,则会出现错误:

    ORA-00918:列定义不明确

    00918. 00000-“列定义不明确

    *原因:

    *行动:

    为了解决这个问题,我们需要给SUMCOUNT子句一个别名:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount) AS sum_sales,

      COUNT(sale_amount) AS count_sales

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    该查询的结果是:

    位置

    1_SUM_SALES

    1_COUNT_SALES

    2_SUM_SALES

    2_COUNT_SALES

    3_SUM_SALES

    3_COUNT_SALES

    4_SUM_SALES

    4_COUNT_SALES

    西方

    545

    1

    (空值)

    0

    1382

    3

    (空值)

    0

    中央

    (空值)

    0

    (空值)

    0

    433

    2

    584

    1

    北方

    1128

    2

    875

    1

    (空值)

    0

    987

    1

    南方

    (空值)

    0

    881

    2

    714

    1

    (空值)

    0

    东方

    192

    1

    147

    1

    407

    1

    785

    2

    您可以看到,对于customer_id的每个值,将显示销售的SUM,然后显示COUNT。列别名已包括customer_idsum_salescount_sales的别名。这使我们可以在多个列上使用Oracle PIVOT

     

    按多列分组

     

    PIVOT查询中使用多列的另一种方法是按多列分组。到目前为止,我们仅按customer_id分组。如果要按customer_id和类别分组怎么办?

    你可以那样做

    SELECT *

    FROM (

      SELECT location, prod_category, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR (customer_id, prod_category)

      IN (

        (1, 'furniture') AS furn1,

        (2, 'furniture') AS furn2,

        (1, 'electronics') AS elec1,

        (2, 'electronics') AS elec2

      )

    );

    在此查询中,FOR子句包括两列:customer_idprod_category。此prod_category也已在开始时添加到SELECT子查询中,因为之前未包含它。

    现在,我们在FOR子句中指定了两列,我们需要将这两列都添加到IN子句中。现在,每个IN标准都包含在方括号中,该方括号指定了customer_idprod_category的值(例如1“ furniture”)。仅在此示例中,我们排除了许多其他记录。

    我们还为他们提供了列别名,以使其更易于阅读。

    如果运行此查询,将得到以下结果:

    位置

    炉1

    炉2

    ELEC1

    ELEC2

    西方

    545

    (空值)

    (空值)

    (空值)

    中央

    (空值)

    (空值)

    (空值)

    (空值)

    北方

    (空值)

    875

    1128

    (空值)

    南方

    (空值)

    503

    (空值)

    378

    东方

    192

    (空值)

    (空值)

    (空值)

    结果显示了家具和电子产品类别以及customer_id 12的销售总和。可以根据需要根据需要在IN子句中定制此处的组。

     

    XMLPIVOT示例

     

    PIVOT关键字允许您以XML格式显示结果。这就像在PIVOT关键字之后添加XML关键字一样简单。使用前面的示例,我们可以以XML格式显示输出。

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT XML (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    如果仅添加XML关键字并运行此查询,则会出现以下错误:

    ORA-00905:缺少关键字

    00905. 00000-“缺少关键字

    *原因:

    *行动:

    这是因为我们无法在IN子句中指定值。我们将需要使用子查询或使用关键字ANY

     

    具有ANYXML

    这是带有XML参数和ANY关键字的查询的示例。

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT XML (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (ANY)

    );

    使用关键字ANY表示对customer_id的所有值进行分组。该查询的结果是:

    位置

    CUSTOMER_ID_XML

    中央

    (XMLTYPE)

    东方

    (XMLTYPE)

    北方

    (XMLTYPE)

    南方

    (XMLTYPE)

    西方

    (XMLTYPE)

    默认情况下,SQL Developer将为这些XML列显示“ XMLTYPE”的值。如果您将其作为脚本(而不是网格)运行,或者作为应用程序的一部分运行,它将返回完整值。如果要在网格中看到它,可以在SQL Developer中更改设置:

    1. 打开工具菜单,然后单击首选项。
    2. 展开数据库部分,然后单击高级。
    3. 选中在网格中显示XML
    4. 点击确定

    现在,您可以重新运行查询,并显示完整值。

    位置

    CUSTOMER_ID_XML

    中央

    <PivotSet> <item> <列名称=“ CUSTOMER_ID”> 3 </ column>…

    东方

    <PivotSet> <item> <列名称=“ CUSTOMER_ID”> 1 </ column>…

    北方

    <PivotSet> <item> <列名=“ CUSTOMER_ID”> 1 </ column>

    南方

    <PivotSet> <item> <列名称=“ CUSTOMER_ID”> 2 </ column>

    西方

    <PivotSet> <item> <列名=“ CUSTOMER_ID”> 1 </ column>

    数据被转换为XML格式。上面的值已缩短以清理表,完整值如下所示:

    <PivotSet><item><column name = "CUSTOMER_ID">3</column><column name = "SALES_TOTAL">433</column></item><item><column name = "CUSTOMER_ID">4</column><column name = "SALES_TOTAL">584</column></item></PivotSet>

    如果您的应用程序正在处理XML,这将很有用。但是,与普通网格相比,它有点难以阅读。

     

    带有子查询的XML

    在使用XML输出的数据透视查询中定义列的另一种方法是使用子查询。我们之前使用关键字ANY的查询在IN子句中定义列。

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT XML (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (ANY)

    );

    可以使用子查询来定义要包括的列值,而不是使用ANY关键字。

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT XML (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (

        SELECT customer_id

        FROM cust_sales_category

      )

    );

    IN子句中的子查询为:

    SELECT customer_id FROM cust_sales_category

    这将从该表中找到所有的customer_id值。其结果与ANY关键字相同。

    使用子查询意味着您可以更好地控制使用哪些组。您可以在此子查询中添加WHERE子句以仅显示某些customer_id值:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT XML (

      SUM(sale_amount) AS sales_total

      FOR customer_id

      IN (

        SELECT customer_id

        FROM cust_sales_category

        WHERE customer_id <= 3

      )

    );

    这将为所有小于或等于3customer_id创建列组。

    我根本没有使用过这个XML输出,但是如果您需要在查询或应用程序中使用它,那么它就非常有用。

     

    动态指定列

     

    在普通的数据透视查询中,或者在将数据输出为列而不是XML的查询中,必须指定要包含在组中的列:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (1, 2, 3, 4)

    );

    如果要按所有这些值分组,是否有办法这么说,而不是单独列出所有值?

    不幸的是,没有。除非您已应用XML关键字,否则您不能在IN子句中使用ANY关键字或使用子查询。

    此查询将返回错误:

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (ANY)

    );

    ORA-00936:缺少表达

    00936. 00000-“缺少表达

    *原因:

    *行动:

     

    使用UNPIVOT将列转换为行

     

    我们上面看过的SQL PIVOT关键字会将行转换为列。Oracle提供了UNPIVOT关键字,其作用相反。它将列转换为行。

    SQL UNPIVOT关键字的语法为:

    SELECT columns

    FROM table

    UNPIVOT [INCLUDE|EXCLUDE NULLS] (

      unpivot_clause,

      unpivot_for_clause,

      unpivot_in_clause

    )

    WHERE criteria;

    它具有与PIVOT关键字类似的结构,但有一些区别:

    • 它允许您以特定方式处理NULL值。
    • 它不包含XML关键字。
    • 它不会取消聚合行,因为查询不了解聚合后的数据。

    有一些带有UNPIVOT关键字的子句:

    • unpivot_clause:这为数据透视表中的每个列值指定列的名称。
    • unpivot_for_clause:这指定数据透视表中显示的数字值的列名称。
    • unpivot_in_clause:这指定了透视列的列表。

    如果这听起来令人困惑,那么一些示例将有助于更好地解释它。

     

    一个简单的UNPIVOT示例

     

    为了演示SQL UNPIVOT关键字,我们需要首先具有一些数据透视。我们可以提供一个子查询,但是UNPIVOT查询将非常混乱。因此,让我们创建一个显示数据透视图的视图

    我们可以创建一个名为pivod_sales的视图。

    CREATE VIEW pivoted_sales AS

    SELECT *

    FROM (

      SELECT location, customer_id, sale_amount

      FROM cust_sales_category

    )

    PIVOT (

      SUM(sale_amount)

      FOR customer_id

      IN (

        1 AS cust1,

        2 AS cust2,

        3 AS cust3,

        4 AS cust4

      )

    );

    创建视图后,我们可以从中选择以检查数据:

    SELECT *

    FROM pivoted_sales;

    位置

    客户1

    客户2

    客户3

    客户4

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    现在,让我们来看一个UNPIVOT关键字的示例。

    SELECT *

    FROM pivoted_sales

    UNPIVOT (

      total_sales

      FOR customer_id

      IN (cust1, cust2, cust3, cust4)

    );

    该查询的结果是:

    位置

    顾客ID

    总销售额

    西方

    客户1

    545

    西方

    客户3

    1382

    中央

    客户3

    433

    中央

    客户4

    584

    北方

    客户1

    1128

    北方

    客户2

    875

    北方

    客户4

    987

    南方

    客户2

    881

    南方

    客户3

    714

    东方

    客户1

    192

    东方

    客户2

    147

    东方

    客户3

    407

    东方

    客户4

    785

    在此查询中:

    • unpivot_clause是“ total_sales”,其结果是最后一列的名称。此列包含每个客户和地区的数字值或销售总额。
    • unpivot_for_clause是“ customer_id”。这是第二列的名称,该列由数据透视表中不同的列标题填充。
    • unpivot_in_clause是来自透视数据的每个列标题。基础表中的列是cust1,cust2,cust3和cust4,这些列在IN子句中指定。

     

    UNPIVOT中处理NULL

     

    在上面的输出中,您会注意到返回了13行。但是,有5个地点和4个客户。该查询是否应该返回20个结果,即5 x 4,并因此得出位置和客户的所有组合?为什么查询不这样做?

    这是因为我们从中选择的数据对于位置和客户的某些组合没有值。“ west”的位置已返回客户13的行,因此这意味着客户24没有行。让我们查询表并进行检查。

    SELECT *

    FROM pivoted_sales;

    位置

    客户1

    客户2

    客户3

    客户4

    西方

    545

    (空值)

    1382

    (空值)

    中央

    (空值)

    (空值)

    433

    584

    北方

    1128

    875

    (空值)

    987

    南方

    (空值)

    881

    714

    (空值)

    东方

    192

    147

    407

    785

    您可以看到位置为“ west”的行中,有一个值cust1cust3,但没有cust2cust4

    Oracle UNPIVOT关键字不显示基础数据为NULL的结果。该表中有7NULL值,因此在20个可能的值中,这7个未显示,因此只有13个值。

    此行为是默认行为,但可以更改。如果在UNPIVOT关键字之后添加关键字INCLUDE NULLS,则结果中的记录将为NULL值。

    SELECT *

    FROM pivoted_sales

    UNPIVOT INCLUDE NULLS (

      total_sales

      FOR customer_id

      IN (cust1, cust2, cust3, cust4)

    );

    位置

    顾客ID

    总销售额

    西方

    客户1

    545

    西方

    客户2

    (空值)

    西方

    客户3

    1382

    西方

    客户4

    (空值)

    中央

    客户1

    (空值)

    中央

    客户2

    (空值)

    中央

    客户3

    433

    中央

    客户4

    584

    北方

    客户1

    1128

    北方

    客户2

    875

    北方

    客户3

    (空值)

    北方

    客户4

    987

    南方

    客户1

    (空值)

    南方

    客户2

    881

    南方

    客户3

    714

    南方

    客户4

    (空值)

    东方

    客户1

    192

    东方

    客户2

    147

    东方

    客户3

    407

    东方

    客户4

    785

    结果显示20行。包括原始的13行以及total_salesNULL值的7行。此处使用的INCLUDE NULLS关键字已导致包含这些值。

    此处可以使用的另一个关键字是EXCLUDE NULLS。这与默认行为相同,类似于许多其他参数,例如默认为ORDER BY ASC。如果您想要某些东方西专门排除NULL值而不依赖默认值,那么最好指定它:

    SELECT *

    FROM pivoted_sales

    UNPIVOT EXCLUDE NULLS (

      total_sales

      FOR customer_id

      IN (cust1, cust2, cust3, cust4)

    );

    这样便可以在UNPIVOT查询中处理NULL值。

     

    别名和UNPIVOT

     

    就像使用PIVOT关键字一样,我们可以使用UNPIVOT将别名应用于数据。但是,我们只能使用pivot_in_clause来执行此操作。例如:

    SELECT *

    FROM pivoted_sales

    UNPIVOT (

      total_sales

      FOR customer_id

      IN (

        cust1 AS 'cust 1 sales',

        cust2 AS 'cust 2 sales',

        cust3 AS 'cust 3 sales',

        cust4 AS 'cust 4 sales'

      )

    );

    该查询的结果是:

    位置

    顾客ID

    总销售额

    西方

    客户1次销售

    545

    西方

    客户销售3

    1382

    中央

    客户销售3

    433

    中央

    客户销售4

    584

    北方

    客户1次销售

    1128

    北方

    客户2销售

    875

    北方

    客户销售4

    987

    南方

    客户2销售

    881

    南方

    客户销售3

    714

    东方

    客户1次销售

    192

    东方

    客户2销售

    147

    东方

    客户销售3

    407

    东方

    客户销售4

    785

    customer_id列中的值已转换为指定的值。它们需要包含在单引号中。如果不是,则会出现错误,如下所示:

    SELECT *

    FROM pivoted_sales

    UNPIVOT (

      total_sales

      FOR customer_id

      IN (

        cust1 AS cust_1_sales,

        cust2 AS cust_2_sales,

        cust3 AS cust_3_sales,

        cust4 AS cust_4_sales

      )

    );

    ORA-56901:数据透视表| unpivot值不允许使用非常数表达式

    56901. 00000-“枢轴值|非枢轴值不允许使用非常数表达式

    *原因:尝试对枢轴值|非枢轴值使用非常数表达式。

    *操作:将常量用于数据透视|取消数据透视。

     

    错误在行:339列:12

    ORA-56901错误(数据透视表值不允许使用非常数表达式)意味着您必须为别名使用常数值。您可以将别名括在单引号中以解决此错误。

     

    您可以不使用Oracle PIVOT进行汇总吗?

     

    不,SQL PIVOT仅适用于聚合函数。如果要在不丢失数据细节的情况下将行转置为列,可以尝试使用MAXMIN函数。

    假设您在bug_result表中有一个错误列表,并且每个阶段都有不同的结果:

    BUG_ID

    阶段

    结果

    1

    开发人员

    通过

    1

    测试

    通过

    1

    UAT

    失败

    2

    开发人员

    通过

    2

    测试

    失败

    3

    开发人员

    通过

    3

    测试

    失败

    3

    UAT

    通过

    如果要在此数据集上使用PIVOT,则可以使用RESULT列上的MAX函数来完成。

    SELECT *

    FROM bug_result

    PIVOT (

      MAX(result)

      FOR stage

      IN ('Dev', 'Test', 'UAT')

    );

    MAXMIN函数与VARCHAR2或文本值一起使用的效果与与日期和数字一样好。因此,如果在数据库上运行此查询,则会得到如下结果:

    BUG_ID

    开发人员

    测试

    UAT

    1

    通过

    通过

    失败

    2

    通过

    失败

    (空值)

    3

    通过

    失败

    通过

    这样便可以在没有聚合的情况下在Oracle SQL中生成数据透视表。总结没有数字值的结果非常好。

     

    结论

     

    Oracle PIVOTUNPIVOT功能强大,是转换数据的非常有用的方法。它们通常与数值一起使用以汇总数据,但也可以与文本和日期值一起使用。

    它包含XML功能,可以XML格式导出数据。您还可以将别名添加到PIVOT行和列,添加WHERE子句,并执行多个聚合和组。

    花一些时间练习使用此便捷的SQL功能,您将能够更好地处理数据。

     

     

    展开全文
  • 使用 PIVOT 和 UNPIVOT

    2014-10-09 14:41:00
    http://msdn.microsoft.com/zh-cn/library/ms177410%28v=SQL.90%29.aspx 可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列...UNPIVOT 与 PIVOT 执行相反的操作,将表值表...

    http://msdn.microsoft.com/zh-cn/library/ms177410%28v=SQL.90%29.aspx

    可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

    ms177410.note(zh-cn,SQL.90).gif注意:

    对升级到 Microsoft SQL Server 2005 的数据库使用 PIVOT 和 UNPIVOT 时,数据库的兼容级别必须设置为 90。有关如何设置数据库兼容级别的信息,请参阅sp_dbcmptlevel (Transact-SQL)

    PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL)

    以下是带批注的 PIVOT 语法。

    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 <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>

    简单 PIVOT 示例

    下面的代码示例生成一个两列四行的表。

    USE AdventureWorks ;
    GO
    SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
    FROM Production.Product
    GROUP BY DaysToManufacture
    
    

    下面是结果集:

    DaysToManufacture          AverageCost

    0                          5.0885

    1                          223.88

    2                          359.1082

    4                          949.4105

    没有定义 DaysToManufacture 为 3 的产品。

    以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天,即使结果为 NULL

    -- Pivot table with one row and five columns
    SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
    [0], [1], [2], [3], [4]
    FROM
    (SELECT DaysToManufacture, StandardCost     FROM Production.Product) AS SourceTable
    PIVOT
    (
    AVG(StandardCost)
    FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
    ) AS PivotTable
    
    

    下面是结果集:

    Cost_Sorted_By_Production_Days    0         1         2           3       4      

    AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

    复杂 PIVOT 示例

    可能会用到 PIVOT 的常见情况是:需要生成交叉表格报表以汇总数据。例如,假设需要在 AdventureWorks 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商排序)。

    USE AdventureWorks;
    GO
    SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
    FROM 
    (SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader) p
    PIVOT
    (
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY VendorID
    

    以下为部分结果集。

    VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
    1           4           3           5           4           4
    2           4           1           5           5           5
    3           4           3           5           4           4
    4           4           2           5           5           4
    5           5           1           5           5           5
    

    将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。

    SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM PurchaseOrderHeader
    

    这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。结果,在透视子句中指定的每个 EmployeeID 号都有相应的一列:在本例中为雇员164198223231233PurchaseOrderID 列作为值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合分组列。请注意,将显示一条警告消息,指出为每个雇员计算 COUNT 时未考虑显示在 PurchaseOrderID 列中的任何空值。

    ms177410.note(zh-cn,SQL.90).gif重要提示:

    如果聚合函数与 PIVOT 一起使用,则计算聚合时将不考虑出现在值列中的任何空值。

    UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符Emp1Emp2Emp3Emp4Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值(Emp1Emp2...)的列将被称为Employee,将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_columnvalue_column。以下为该查询。

    --Create the table and insert values as portrayed in the previous example.
    CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int)
    GO
    INSERT INTO pvt VALUES (1,4,3,5,4,4)
    INSERT INTO pvt VALUES (2,4,1,5,5,5)
    INSERT INTO pvt VALUES (3,4,3,5,4,4)
    INSERT INTO pvt VALUES (4,4,2,5,5,4)
    INSERT INTO pvt VALUES (5,5,1,5,5,5)
    GO
    --Unpivot the table.
    SELECT VendorID, Employee, Orders
    FROM 
       (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
       FROM pvt) p
    UNPIVOT
       (Orders FOR Employee IN 
          (Emp1, Emp2, Emp3, Emp4, Emp5)
    )AS unpvt
    GO
    

    以下为部分结果集。

    VendorID   Employee   Orders
    1      Emp1         4
    1      Emp2         3
    1      Emp3         5
    1      Emp4         4
    1      Emp5         4
    2      Emp1         4
    2      Emp2         1
    2      Emp3         5
    2      Emp4         5
    2      Emp5         5
    ...
    

    请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。

    AdventureWorks 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要在 SQL Server Management Studio 中编写视图脚本,请在“对象资源管理器”中,在“视图”文件夹下找到 AdventureWorks 数据库对应的视图。右键单击该视图名称,再选择“编写视图脚本为”

    转载于:https://www.cnblogs.com/fengye87626/p/4013316.html

    展开全文
  • Sql中的转置

    2018-03-05 17:27:39
    UNPIVOT 操作符说明 简而言之,UNPIVOT操作符就是取得一个行的数据集合,然后把每一行都转换成多个行数据。为了更好地理解,请看下图: 图1 从上图中,你能发现UNPOVOT操作符,取得了两行数据,每行包含三个...
  • 关于 PIVOT 和 UNPIVOT

    2011-04-29 20:52:00
    可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 ...
  • ...
  • Transact-SQL 分析器支持 Unicode 3.2 标准,该标准更改了目前禁止在未分隔标识符中使用的一些国际字符的字符分类。 低 在出现浮点域错误(即 log() 函数的参数为负)时,SET ANSI_WARNINGS ON 不覆盖 ...
  • 达梦数据库中有一个视图,可以...103 无效的表名 104 删除0行记录 105 插入0行记录 106 更新0行记录 107 跨语句游标操作 108 回收权限时无相应权限 109 试图转换空字符串 110 编译没有结束 111 结果集数据获取完成 112
  • 103 无效的表名 104 删除0行记录 105 插入0行记录 106 更新0行记录 107 跨语句游标操作 108 回收权限时无相应权限 109 试图转换空字符串 110 编译没有结束 111 结果集数据获取完成 112 不支持UTF8 113 结果集缓存满 ...
  • SQL Server 2005 Beta 2 Transact-SQL 增强功能
  • 语法 sp_dbcmptlevel [ [ @dbname = ] name ]  [ , [ @new_cmptlevel = ] version ] 参数 [ @dbname = ] ...数据库名称必须符合标识符的规则。nam
  • 无论兼容级别如何,查询 SELECT single_pages_kb FROM sys.dm_os_sys_info 都会生成错误 207(列名无效)。 删除了系统对象。 在 SQL Server 2012 (11.x) 中,sp_dboption 已删除。 无论兼容级别如何,语句 EXEC sp...
  • 数据库引擎错误

    2020-11-09 22:15:01
    ’ 不是有效的标识符。 204 20 Yes 在节点 %1! 中出现规范化错误。 205 16 No 使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。 206 16 No 操作数类型冲突: %1! 与 %2!...
  • 设置数据库兼容级别的两种方法 分类: 数据库管理维护 原创与心得 2010-06-26 19:19 ...包含字符 0xFFFF 的对象名是无效标识符,不能访问。 若要以兼容级别 90 运行,必须重命名包含此字符的对象。 ...
  • unpivot(字段名 for 与字段名不同的标识符 in(表中字段名包含该字段名,类型一致)); select mgr from emp where mgr is not null group by mgr order by mgr desc;--desc降序关键字 select mgr from emp where mgr is...
  • 数据库名称必须符合标识符的规则。 name 的数据类型为 sysname ,默认值为 NULL。 [ @new_cmptlevel = ] version 数据库要与之兼容的 SQL Server 的版本。 version 的数据类型为 tinyint ,默认值为...
  •  都会生成错误 207(列名无效)。 删除了系统对象。  在  SQL Server 2012 (11.x) 中, sp_dboption  已删除。  无论兼容级别如何,语句  EXEC sp_dboption 'AdventureWorks2016CTP3', 'autoshrink', '...
  • http://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=truehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp ...
  • SQLServer之创建索引视图

    万次阅读 2018-10-25 16:00:54
    视图名称必须符合有关标识符的规则。 可以选择是否指定视图所有者名称。 [dbo][.]视图名称 --视图中的列使用的名称。 仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有...
  • SQL Server 2005 术语词汇表

    千次阅读 2011-10-08 11:56:18
    术语 定义 ActiveX 数据对象 (ActiveX Data Objects) 一种易于使用的应用程序编程接口 (API),用于封装 OLE DB 以在诸如 Visual Basic、Visual Basic for Applicatio
  • PIVOT 和 UNPIVOT PIVOT 和 UNPIVOT 是可以在查询的 FROM 子句中指定的新的关系运算符。它们对一个输入表值表达式执行某种操作,并且产生一个输出表作为结果。PIVOT 运算符将行旋转为列,并且可能同时执行聚合。它...

空空如也

空空如也

1 2 3
收藏数 49
精华内容 19
关键字:

unpivot标识符无效