我有一个表格,其结构和示例数据如下:
ITEM LOC STOCK
0001 KS5 10
0001 KS6 30
0002 KS5 10
0002 KS6 20
我需要查询交叉表,以便获得相应的结果。
ITEM KS5 KS6
0001 10 30
0002 10 20
LOC (KS5和KS6) 可以变化,也可以添加新的位置。
我该如何获得所需的结果?
SELECT *
FROM (SELECT ITEM ,LOC ,STOCK
FROM TABLE_NAME)
PIVOT (SUM(STOCK) FOR (LOC) IN ('KS5' , 'KS6'))
ORDER BY ITEM;
敬礼。
对于动态生成的结果,您需要一些动态的PLSQL解决方案,例如创建视图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
中选择结果: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
中出现新值时,您需要在从视图中选择之前执行该过程。SELECT *
FROM (SELECT ITEM ,LOC ,STOCK
FROM TABLE_NAME)
PIVOT (SUM(quantity) AS sum_quantity FOR (ITEM) IN (SELECT DISNTINCT(LOC) FROM TABLE_NAME))
ORDER BY ITEM;