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
The LOC (KS5 and KS6) can vary and new locations can be added.
How can I get the desired result?
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;
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
Every time when new values in column loc appears you need to execute procedure before selecting from view.
Please try this query .
SELECT * FROM (SELECT ITEM ,LOC ,STOCK FROM TABLE_NAME) PIVOT (SUM(quantity) AS sum_quantity FOR (ITEM) IN ('KS5' , 'KS6')) ORDER BY ITEM;