sql - 将行转置为 Oracle Sql 中的列

Question

我希望行中的项目是列。

我已经用数据设置了一个 DBFiddle。链接：https ://dbfiddle.uk/?rdbms=oracle_18&fiddle=122a2865fb645e2e226928e28284c590

这是我的桌子。

我希望输出如下：

如何在 Oracle SQL 中执行此操作？序列和主键让我感到困惑。

标签： sqloracleplsqltranspose

Answer 1

像这样的东西？

SQL> select
  2    id,
  3    max(decode(things, 'Food' , descr)) as food,
  4    max(decode(things, 'Cars' , descr)) as cars,
  5    max(decode(things, 'Sport', descr)) as sport
  6  from abc
  7  group by id
  8  order by id;

        ID FOOD       CARS       SPORT
---------- ---------- ---------- ----------
         1 Chicken    BMW        Soccer
         2 Mutton     Ford       Tennis

SQL>

---

正如您对 PL/SQL 的要求，返回 refcursor 的函数可能是一种选择：

SQL> create or replace function f_abc return sys_refcursor is
  2    l_rc sys_refcursor;
  3  begin
  4    open l_rc for
  5      select
  6        id,
  7        max(decode(things, 'Food' , descr)) as food,
  8        max(decode(things, 'Cars' , descr)) as cars,
  9        max(decode(things, 'Sport', descr)) as sport
 10      from abc
 11      group by id
 12      order by id;
 13    return l_rc;
 14  end;
 15  /

Function created.

SQL> select f_abc from dual;

F_ABC
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

        ID FOOD       CARS       SPORT
---------- ---------- ---------- ----------
         1 Chicken    BMW        Soccer
         2 Mutton     Ford       Tennis


SQL>