Oracle10G SQL:将列转换为行 [英] Oracle10G SQL : Turning Columns into rows

问题描述

我需要将列转换为行并获取其平均值.

I need to turn columns into rows and get its average.

例如，我有此表:

Name   Math    Science     Computer
----   ----    -------     --------
Ted    90       89          95
Zed    99       98          98
Fed    85       75          90

输出应为:

Subject      Average
-------      -------
Math         88
Science      87.33
Computer     94.33

我该如何实现?谢谢您的帮助.

How can I achieve it? Thank you for the help.

推荐答案

如果您使用的是11G，则可以使用

If you were on 11G you could use unpivot:

SELECT subject, AVG(percentage) AS percentage
FROM (
    SELECT * FROM tablea
    UNPIVOT (percentage FOR subject IN (math, science, computer))
)
GROUP BY subject
ORDER BY subject;

SUBJECT  PERCENTAGE
-------- ----------
COMPUTER      94.33
MATH          91.33
SCIENCE       87.33

但是由于您不是，所以您可以伪造它.改编自此网站:

But since you are not, you can fake it. Adapting from this site:

SELECT subject, AVG(percentage) AS percentage
FROM (
    SELECT DECODE(unpivot_row, 1, 'Math',
                               2, 'Science',
                               3, 'Computer') AS subject,
           DECODE(unpivot_row, 1, math,
                               2, science,
                               3, computer) AS percentage
    FROM tablea
    CROSS JOIN (SELECT level AS unpivot_row FROM dual CONNECT BY level <= 3)
)
GROUP BY subject
ORDER BY subject;

SUBJECT  PERCENTAGE
-------- ----------
Computer      94.33
Math          91.33
Science       87.33

在两种情况下，内部的select都将行转换为列；在10克中，您只需要自己做即可. SELECT ... CONNECT BY ...只是生成一个虚拟值列表，它必须足够覆盖要转换为行的列数(如果确实有1000个，则应该重新访问数据模型).这两个decode语句使用该生成的数字来匹配列名和值-自行运行内部选择来确定其外观.

In both cases, the inner select is transforming rows into columns; in 10g you just have to do it yourself. The SELECT ... CONNECT BY ... just generates a list of dummy values, and this has to have enough to cover the number of columns you are converting to rows (and if you really have 1000, you should really revisit the data model). The two decode statements use that generated number to match up a column name and value - run the inner select on its own to se what that looks like.

如果不求助于动态SQL，就无法不必列出列-真正的unpivot只能列出一次，而假的10g版本只能列出两次，并且必须确保它们正确匹配，并且行号生成器正在生成足够的值. (太多了，您可能会得到奇怪的结果，但是由于此处任何多余的值都为null并且您使用的是avg，因此在这种情况下并没有太大关系；就像健全性检查一样，您可能应该使它匹配无论如何).

Without resorting to dynamic SQL, you can't get away from having to list the columns - only once with the real unpivot, but twice with the fake 10g version, and you have to make sure they match up properly, and that the row number generator is producing enough values. (Too many and you might get odd results, but as any extra values will be null here and you're using avg, it doesn't matter too much in this case; just as a sanity check you should probably make it match exactly anyway).

或另一个版本，基于您总是想要除name之外的所有列，这意味着您只需要列出您想要的列一次，并且在视觉上将它们匹配起来更容易-只需不断添加when子句即可；而且您不需要行数:

Or another version, based on you always wanting all the columns except name, which means you only need to list the columns you do want once and it's easier to match them up visually - just keep adding when clauses; and you don't need the row count:

SELECT subject, AVG(percentage) AS percentage
FROM (
    SELECT column_name AS subject,
        CASE
            WHEN column_name = 'MATH' then math
            WHEN column_name = 'SCIENCE' then science
            WHEN column_name = 'COMPUTER' then computer
        END AS percentage
    FROM tablea
    CROSS JOIN (
        SELECT column_name
        FROM user_tab_columns
        WHERE table_name = 'TABLEA'
        AND column_name != 'NAME'
    )
)
GROUP BY subject
ORDER BY subject;

SUBJECT                        PERCENTAGE
------------------------------ ----------
COMPUTER                            94.33
MATH                                91.33
SCIENCE                             87.33

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