“枢轴"表Oracle-如何将行项目更改为列 [英] "pivot" table Oracle - how to change row items into columns

问题描述

我有一个简单的例子:

CREATE TABLE Cars ( Cars, Item, Value ) AS
SELECT 'bmw',      'wheels', '4'      FROM DUAL UNION ALL
SELECT 'bmw',      'color',  'red'    FROM DUAL UNION ALL
SELECT 'bmw',      'price',  '5'      FROM DUAL UNION ALL
SELECT 'mercedes', 'wheels', '4'      FROM DUAL UNION ALL
SELECT 'mercedes', 'color',  'black'  FROM DUAL UNION ALL
SELECT 'lambo',    'wheels', '5'      FROM DUAL UNION ALL
SELECT 'lambo',    'color',  'yellow' FROM DUAL UNION ALL
SELECT 'lambo',    'price',  '7'      FROM DUAL UNION ALL
SELECT 'mercedes', 'price',  '6'      FROM DUAL;

问题是，我需要透视"表以获取具有列值的项目，并以值作为值并将所有内容组合在一起(行具有唯一的汽车名称，并且单元格中没有空值).因此，我写了"pivot"，因为经典的透视(DECODE)会导致不同的结果-减少单元格中的偏斜值和许多空值.所以看起来像这样

The thing is that I need to "pivot" the table to get items as column names with values as values and everything grouped together (row with unique car name and no null values in cells). Hence I wrote "pivot", because classic pivoting (DECODE) leads to different result - decreasing skew values in cells and a lot of null values. So it would looks like this

car      wheels color  price
-------- ------ ------ -----
bmw           4    red     5
lambo         5 yellow     7
mercedes      4  black     6

是这样的问题:

我应该通过程序执行此操作，还是有其他更优雅的解决方案?过程如下(使用伪代码):

Should I do this via procedures or is there any more elegant solution? Procedure would be following (in pseudocode):

1. create table cars2 /*collumns are known in before, wheels/color/price*/
2. get the distinct names of the cars and insert them into collection /*eg nested table*/
3.    for each car do
insert into table cars2
values per item /*looping items and inserting corresponding values*/

好的，代码看起来很简单.但是拥有超过半数的记录和15个项目，并且每小时更新一次表可能会导致实际的性能问题.

OK, code seems simple. But having more than half a milion of records and 15 items, and updating the table once a hour may lead to real performance problems.

推荐答案

请尝试以下查询；-

  select * from
  (select cars, item,value from carTable) 
  pivot(max(value) for item in ('wheels', 'color', 'price'))

这篇关于“枢轴"表Oracle-如何将行项目更改为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！