使用没有连接的聚合创建数据透视表 [英] create pivot table with aggregates without a join

问题描述

我想我正在尝试做一些无法完成的事情.我正在尝试创建一个数据透视表，通过聚合两个不同的列同时进行两个数据透视.我创建了一个非常简化的示例，以使这一点更易于理解.

I think I am trying to do something that cannot be done. I am trying to create a pivot table, simultaneously doing two pivots by aggregating off two different columns. I have created a much simplified example to make the point more understandable.

CREATE TABLE two_aggregate_pivot (
    ID INT,
    category CHAR(1),
    value INT
)

INSERT INTO dbo.two_aggregate_pivot
    ( ID, category, value )
VALUES  (1, 'A', 100),
        (1, 'B', 97),
        (1, 'D', NULL),
        (2, 'A', 86),
        (2, 'C', 83),
        (2, 'D', 81)            

我可以通过以下方式获得类别的计数:

I can pivot to get the count of the categories as follows:

SELECT  piv1.ID,
        [A] AS cat_A,
        [B] AS cat_B,
        [C] AS cat_C,
        [D] AS cat_D
FROM
(SELECT ID, category FROM dbo.two_aggregate_pivot) SRC
PIVOT
(
    COUNT(category)
    FOR category IN ([A],[B],[C],[D])
) piv1

我得到了我想要的.

ID  cat_A   cat_B   cat_C   cat_D
1   1       1       0       1
2   1       0       1       1

所以我也可以编写一个完全独立的查询，在源选择中添加值列，然后从 MAX(value) 聚合，并获得最大值的枢轴.

So too I can write a totally separate query, add the value column in the source select, and aggregate from MAX(value) instead, and get a pivot of max values.

ID  val_A   val_B   val_C   val_D
1   100     97      NULL    NULL
2   86      NULL    83      81

但我不知道如何获得它们.

But what I can't figure out is how to get them both.

ID  cat_A   cat_B   cat_C   cat_D    val_A  val_B   val_C   val_D
1   1       1       0       1        100     97      NULL    NULL
2   1       0       1       1        86      NULL    83      81

我在 stackoverflow 上看到过使用 CASE 语句检查 IS NOT NULL 的示例，但我认为这对我不起作用，因为我可以同时拥有缺失值和真正为 NULL 的现存值.我可以创建两个 CTE，每个 PIVOT 一个，然后加入它们.这给了我想要的表，但它强制对表进行两次聚集索引扫描，然后当然是连接运算符.该表非常大，性能很重要，所以我想尝试找到一种方法，在同一个聚集索引扫描中执行两个数据透视.

I have seen examples here on stackoverflow using CASE statements to check IS NOT NULL, but that doesn't work for me I don't think, since I can have both missing values and extant values that are really NULL. I can create two CTEs, one with each PIVOT, then JOIN them. That gives me the table that I want, but it forces a clustered index scan of the table twice, and then of course the join operator. The table is quite large, and performance matters, so I want to try to find a way to do both pivots within the same clustered index scan.

这可能吗?

推荐答案

也许可以使用 CROSS APPLY 来取消数据透视.我应该补充一点，动态化是一件小事.

Perhaps a CROSS APPLY to unpivot your data. I should add, it would be a small matter to go dynamic.

示例

Select *
 From  (
            Select ID
                  ,B.*
             From  two_aggregate_pivot A
             Cross Apply (values ('cat_'+category ,1)
                                ,('val_'+category ,value)
                         ) B (Item,Value)
       ) src
  Pivot (sum(value) for item in ([cat_A],[cat_B],[cat_C],[cat_D],[val_A],[val_B],[val_C],[val_D]) ) pvt

退货

ID  cat_A   cat_B   cat_C   cat_D   val_A   val_B   val_C   val_D
1   1       1       NULL    1       100     97      NULL    NULL
2   1       NULL    1       1       86      NULL    83      81

这篇关于使用没有连接的聚合创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！