将行转置为Oracle 11g中的列 [英] Transpose rows to columns in Oracle 11g

问题描述

我有一个包含值的表格

Hi,
I have a table that contains the values

ItemId|ItemValue|Cost
______________________
1   A   20
1   A   38
3   B   450
4   B   28
5   C   46
6   D   87

请找到相同的DB脚本

Please find the DB Scripts for the same

CREATE TABLE Item (
  ItemId NUMBER,
  ItemValue VARCHAR2(20),
  Cost Number
);

INSERT INTO Item VALUES (1,'A',20);
INSERT INTO Item VALUES (1,'A',38);
INSERT INTO Item VALUES (3,'B',450);
INSERT INTO Item VALUES (4,'B',28);
INSERT INTO Item VALUES (5,'C',46);
INSERT INTO Item VALUES (6,'D',87);

我想查询显示如下的值

I want the Query that Display the values like this

ItemValue  1    3     4    5     6
_____________________________
A         20
A         38
B              450
B               28
C                    28    

我将使用Oracle作为我的数据库

I will be using Oracle as my DB

推荐答案

首先感谢您包含代码段以创建示例数据。不幸的是，我无法完全测试这个，因为sqlfiddle已关闭，我不再在这台PC上使用Oracle :(



但是......你需要 PIVOT [ ^ ]将行转置为列的表 - 进一步透视示例 [ ^ ]



我在这里看到的第一个问题是你在ItemValue的预期结果中有多行 - 通常使用PIVOT你会使用一个函数，如MIN，MAX，SUM，COUNT - 如果这真的是你可以用UNPIVOT（上面相同的链接）来实现它



你可以用这个sql

Firstly Thank You for including the code snippet to create the sample data. Unfortunately I haven't been able to test this fully as sqlfiddle is down and I no longer have Oracle on this PC :(

However... you will need to PIVOT[^] the table to transpose the rows to columns - further pivot examples[^]

The first problem I see here though is that you have multiple rows in your expected results for ItemValue - usually with PIVOT you would use a function such as MIN, MAX, SUM, COUNT - If this is genuinely what you are after you might be able to achieve it with UNPIVOT (same links as above)

You could achieve it with this sql

select ItemValue, case when ItemValue = 'A' then Cost ELSE null END,
        case when ItemValue = 'B' then Cost ELSE null END,
        case when ItemValue = 'C' then Cost ELSE null END,
        case when ItemValue = 'D' then Cost ELSE null END
from Item
order by ItemValue

但这不是可扩展的，我非常怀疑它是你需要的。

but this is not extendable and I very much doubt that it is what you need.

你必须通过添加作弊每行的唯一值。像这样：

You'll have to cheat then, by adding a unique value for every row. Like this:

SELECT  ItemValue,"1","3","4","5"
FROM    (
    SELECT  *
    FROM    (
        SELECT  ItemId
               ,ItemValue
               ,Cost
               ,Row_Number() OVER (ORDER BY itemid,itemvalue) rn
        FROM    Item
        )
pivot (
    Max(cost)
    for itemid
    IN  (1,3,4,5)
    )
    )

这里 [ ^ ]小提琴。

这篇关于将行转置为Oracle 11g中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！