Oracle SQL Cross Tab Query

Question

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?

It's called pivoting, isn't it? – Florin Ghita May 04 '15 at 06:31 — Florin Ghita, May 04 '15 at 06:31

score 2 · Answer 1 · edited Apr 20 '23 at 09:37

2

Please try this query .

SELECT *
FROM   (SELECT ITEM ,LOC ,STOCK
        FROM   TABLE_NAME)
PIVOT  (SUM(STOCK) FOR (LOC) IN ('KS5' , 'KS6'))
ORDER BY ITEM;

Regards.

edited Apr 20 '23 at 09:37

jumping_monkey

5,941
2
43
58

answered May 04 '15 at 06:34

Jerry

19
3

Hi Florin, Thanks. Is there any way that instead of specifying KS5 and KS6, I write a subquery that would pick the locations dynamically as new locations would also be added later. – Imran Hemani May 04 '15 at 07:16

score 1 · Accepted Answer · answered May 04 '15 at 13:59

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.

score 0 · Answer 3 · edited Apr 17 '18 at 10:18

0

Try and modify SQL as below:

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;

edited Apr 17 '18 at 10:18

Pang

9,564
146
81
122

answered Apr 17 '18 at 10:05

Ogisi Matthew

1
2

Oracle SQL Cross Tab Query

3 Answers3

Linked