SQL 在 Oracle 行的多列上使用数据透视

Using pivot on multiple columns of an Oracle row

提问by Badal

I have the following sample data in an Oracle table (tab1) and I am trying to convert rows to columns. I know how to use Oracle pivot on one column. But is it possible to apply it to multiple columns?

我在 Oracle 表 ( tab1) 中有以下示例数据，我正在尝试将行转换为列。我知道如何在一列上使用 Oracle 数据透视表。但是是否可以将其应用于多列？

Sample data:

样本数据：

Type  weight  height  
A     50      10  
A     60      12  
B     40      8  
C     30      15  

My intended output:

我的预期输出：

A-count B-count C-count A-weight B-weight C-weight A-height B-height C-height  
2       1       1       110      40       30       22       8        15  

What I can do:

我可以做什么：

with T AS 
(select type, weight from tab1 )
select * from T
PIVOT (
count(type)
for type in (A, B, C, D,E,F)
)  

The above query gives me the below result

上面的查询给了我下面的结果

A B C  
2 1 1  

I can replace count(*)with sum(weight)or sum(height)to pivot height or weight. What I am looking to do, but I can't do, is pivot on all three (count, weight and height) in one query.

我可以替换count(*)为sum(weight)或sum(height)枢轴高度或重量。我想做但我不能做的是在一个查询中以所有三个（计数、重量和高度）为中心。

Can it be done using pivot?

可以使用pivot来完成吗？

回答by Alex Poole

As the documentation shows, you can have multiple aggregate function clauses. So you can do this:

正如文档所示，您可以有多个聚合函数子句。所以你可以这样做：

select * from (
  select * from tab1
)
pivot (
  count(type) as ct, sum(weight) as wt, sum(height) as ht
  for type in ('A' as A, 'B' as B, 'C' as C)
);

A_CT A_WT A_HT B_CT B_WT B_HT C_CT C_WT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
   2  110   22    1   40    8    1   30   15 

If you want the columns in the order you showed then add another level of subquery:

如果您想要按显示顺序排列的列，请添加另一个级别的子查询：

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht
from (
  select * from (
    select * from tab1
  )
  pivot (
    count(type) as ct, sum(weight) as wt, sum(height) as ht
    for type in ('A' as A, 'B' as B, 'C' as C)
  )
);

A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT
---- ---- ---- ---- ---- ---- ---- ---- ----
   2    1    1  110   40   30   22    8   15 

SQL Fiddle.

SQL小提琴。

回答by Amit S

The second approach to name the columns is even better and solves more problems. I had a requirement where I wanted to sum up the data returned from PIVOT so having column names I could simply add 2 and get the required result in third one -

第二种命名列的方法更好，可以解决更多问题。我有一个要求，我想总结从 PIVOT 返回的数据，因此列名我可以简单地添加 2 并在第三个中获得所需的结果 -

select a_ct, b_ct, c_ct, a_wt, b_wt, c_wt, a_ht, b_ht, c_ht, a_wt + b_wt + c_wt tot_wt
from (
  select * from (
    select * from tab1
  )
  pivot (
    count(type) as ct, sum(weight) as wt, sum(height) as ht
    for type in ('A' as A, 'B' as B, 'C' as C)
  )
);

A_CT B_CT C_CT A_WT B_WT C_WT A_HT B_HT C_HT TOT_WT
---- ---- ---- ---- ---- ---- ---- ---- ---- ------
   2    1    1  110   40   30   22    8   15 180

Just beware that aggregate functions (like sum) won't behave as expected if one of the PIVOT column used returns null, in that case I have used CASE statement to get around it.

请注意，如果使用的 PIVOT 列之一返回 null，聚合函数（如 sum）将不会按预期运行，在这种情况下，我使用 CASE 语句来解决它。

Hope it helps someone.

希望它可以帮助某人。

回答by SherlockSpreadsheets

The answer from Alex Poole is awesome and helped my with my Oracle query. This made me curious and here I will quickly point out syntax comparison for Oracle for Microsoft PIVOT multiple columns. This is one area where I would actually award Oracle the SQL Simpler Syntax Award (it's a made up award created by me).

Alex Poole 的回答很棒，帮助我完成了 Oracle 查询。这让我很好奇，在这里我将快速指出 Oracle for Microsoft PIVOT 多列的语法比较。这是我实际上将 SQL Simpler Syntax Award 授予 Oracle 的一个领域（这是我创建的一个虚构奖项）。

Microsoft SQL is not nearly as simple and flexible when needing to pivot multiple columns.

当需要旋转多个列时，Microsoft SQL 几乎没有那么简单和灵活。

• You have to concatenate the original grouping field to make it distinct
• The column name aliasing is much more manual in MSFT SQL; The code below doesn't have aliasing
• You have to do mutiple PIVOT calls
• You must alias the PIVOT function to avoid syntax error
• Even with all these things considered it still doesn't group as desired
• Bottom line, Oracle wins the the SQL Simpler Syntax Award

• 您必须连接原始分组字段以使其不同
• 列名别名在 MSFT SQL 中更加手动；下面的代码没有别名
• 你必须做多个 PIVOT 调用
• 您必须为 PIVOT 函数设置别名以避免语法错误
• 即使考虑了所有这些事情，它仍然没有按预期分组
• 最重要的是，Oracle 赢得了 SQL Simpler Syntax Award

Given the same data set:

给定相同的数据集：

DECLARE @tblSampleData AS table (Type nvarchar(10), Weight numeric(5,2), Height numeric(5,2))

INSERT INTO @tblSampleData (Type, Weight, Height)
VALUES
('A',     50,      10) 
,('A',     60,      12) 
,('B',     40,      8 )
,('C',     30,      15)

Microsoft SQL:

微软 SQL：

select * from 
(
    select 
        *
        ,concat(Type,'1') as "Type1" 
        ,concat(Type,'2') as "Type2"
    from @tblSampleData
) AS src
pivot (
    count(Type) --as ct, sum(weight) as wt, sum(height) as ht
    for Type in ([A], [B], [C])
) AS pvt1
pivot (
    sum(weight) --as ct, sum(weight) as wt, sum(height) as ht
    for Type1 in ([A1], [B1], [C1])
) AS pvt1
pivot (
    sum(height) --as ct, sum(weight) as wt, sum(height) as ht
    for Type2 in ([A2], [B2], [C2])
) AS pvt1