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?

Answers


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;

Regards.


Need Your Help

Delphi Now() function returns a wrong value

delphi datetime delphi-2007

I have the DirectX-based application. And recently I found that Now() function returns a wrong value when being called from within the main loop of my graphics engine. It gives one value being called

onTouchEvent for multiple images in view

java android

In my Java class we are doing some Android code.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.