Oracle SQL交叉表查询 [英] Oracle SQL Cross Tab Query

问题描述

我有一个表，该表具有以下结构和示例数据:

I have a table which has the following structure and sample data:

ITEM   LOC   STOCK
0001   KS5    10
0001   KS6    30
0002   KS5    10
0002   KS6    20

我需要查询交叉表，以便获得

I need to query cross tab so that I get

ITEM  KS5  KS6
0001  10   30
0002  10   20

LOC(KS5和KS6)可能有所不同，并且可以添加新位置.

The LOC (KS5 and KS6) can vary and new locations can be added.

如何获得所需的结果?

推荐答案

要动态生成结果，您需要一些动态PLSQL解决方案，类似于此过程，它可以创建视图 v_list_loc :

For dynamically generated results you need some dynamic PLSQL solution, something like this procedure creating view v_list_loc:

create or replace procedure p_list_loc is

  v_sql varchar2(32000) := '';

begin

  for c in (select distinct loc from test order by loc) loop
    v_sql := v_sql || '''' ||c.loc|| ''' '||c.loc||',';
  end loop;

  v_sql := 'create or replace view v_list_loc as '
    ||'select * from (select item, loc, stock from test) pivot (sum(stock) '
    ||'for (loc) in ('||rtrim(v_sql, ',')||'))';

  execute immediate v_sql;

end p_list_loc;

在过程代码中，将 test 替换为您的表名.编译此过程，运行并从生成的视图 v_list_loc 中选择结果:

In procedure code replace test with your table name. Compile this procedure, run and select results from generated view v_list_loc:

SQL> exec p_list_loc;

PL/SQL procedure successfully completed

SQL> select * from v_list_loc;

ITEM         KS5        KS6
----- ---------- ----------
0001          10         30
0002          10         20

每当出现 loc 列中的新值时，您都需要先执行过程，然后再从视图中进行选择.

Every time when new values in column loc appears you need to execute procedure before selecting from view.

这篇关于Oracle SQL交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！