sql - 没有 UNPIVOT 的 Oracle SQL 列到行

Question

我目前拥有的:

Team    User    Apples    Oranges    Pears
Red     Adam    4         5          6
Red     Avril   11        12         13
Blue    David   21        22         23

需要什么:

Team    User    Product    Count
Red     Adam    Apples     4
Red     Adam    Oranges    5
Red     Adam    Pears      6
Red     Avril   Apples     11
Red     Avril   Oranges    12
Red     Avril   Pears      13
Blue    David   Apples     21
....

这将使用 Oracle SQL 来实现。我知道这可以使用 UNPIVOT 来完成，但是我的 Oracle SQL 版本太旧，不支持这种方法。有人可以举例说明如何使用 CROSS APPLY 或等效方法实现此目的吗？ 计数变化取决于团队-用户-产品组合，并且产品类型的数量将来可能会略有变化，因此可能需要可扩展的解决方案。

这是时间敏感的，因此非常感谢您的帮助。

Answer 1

您可以使用交叉连接和一些 case 语句来执行此操作，方法是使用一个虚拟子查询，该子查询包含与您想要取消透视的列相同的行数(因为您希望每一列都进入其自己的行)，例如所以:

WITH your_table AS (SELECT 'Red' Team, 'Adam' usr, 4 Apples, 5 Oranges, 6 Pears FROM dual UNION ALL
                    SELECT 'Red' Team, 'Avril' usr, 11 Apples, 12 Oranges, 13 Pears FROM dual UNION ALL
                    SELECT 'Blue' Team, 'David' usr, 21 Apples, 22 Oranges, 23 Pears FROM dual)
-- end of mimicking your table. See SQL below:
SELECT yt.team,
       yt.usr,
       CASE WHEN d.id = 1 THEN 'Apples'
            WHEN d.id = 2 THEN 'Oranges'
            WHEN d.id = 3 THEN 'Pears'
       END product,
       CASE WHEN d.id = 1 THEN yt.apples
            WHEN d.id = 2 THEN yt.oranges
            WHEN d.id = 3 THEN yt.pears
       END count_of_product
FROM   your_table yt
       CROSS JOIN (SELECT LEVEL ID
                   FROM   dual
                   CONNECT BY LEVEL <= 3) d -- number of columns to unpivot
ORDER BY team, usr, product;

TEAM USR   PRODUCT COUNT_OF_PRODUCT
---- ----- ------- ----------------
Blue David Apples                21
Blue David Oranges               22
Blue David Pears                 23
Red  Adam  Apples                 4
Red  Adam  Oranges                5
Red  Adam  Pears                  6
Red  Avril Apples                11
Red  Avril Oranges               12
Red  Avril Pears                 13

这样做意味着您只需遍历表格一次，而不是如果您使用 union all 方法多次。

---

预计到达时间:这是 Aleksej 所指的方法 - 我建议针对您的数据集(希望数据足够大以具有代表性)测试这两种方法，以查看哪种方法性能更高:

WITH your_table AS (SELECT 'Red' Team, 'Adam' usr, 4 Apples, 5 Oranges, 6 Pears FROM dual UNION ALL
                    SELECT 'Red' Team, 'Avril' usr, 11 Apples, 12 Oranges, 13 Pears FROM dual UNION ALL
                    SELECT 'Blue' Team, 'David' usr, 21 Apples, 22 Oranges, 23 Pears FROM dual)
-- end of mimicking your table. See SQL below:
SELECT yt.team,
       yt.usr,
       CASE WHEN LEVEL = 1 THEN 'Apples'
            WHEN LEVEL = 2 THEN 'Oranges'
            WHEN LEVEL = 3 THEN 'Pears'
       END product,
       CASE WHEN LEVEL = 1 THEN yt.apples
            WHEN LEVEL = 2 THEN yt.oranges
            WHEN LEVEL = 3 THEN yt.pears
       END count_of_product
FROM   your_table yt
CONNECT BY PRIOR team = team
           AND PRIOR usr = usr
           AND PRIOR sys_guid() IS NOT NULL
           AND LEVEL <= 3
ORDER BY team, usr, product;

TEAM USR   PRODUCT COUNT_OF_PRODUCT
---- ----- ------- ----------------
Blue David Apples                21
Blue David Oranges               22
Blue David Pears                 23
Red  Adam  Apples                 4
Red  Adam  Oranges                5
Red  Adam  Pears                  6
Red  Avril Apples                11
Red  Avril Oranges               12
Red  Avril Pears                 13