oracle plsql select pivot without dynamic sql to group by
- by kayhan yüksel
To whom it may respond to,
We would like to use SELECT function with PIVOT option at a 11g r2 Oracle DBMS.
Our query is like :
"select * from
(SELECT o.ship_to_customer_no, ol.item_no,ol.amount
FROM t_order o, t_order_line ol
WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line))
pivot --xml
( SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line));"
As can be seen, XML is commented out, if run as PIVOT XML it gives the correct output in XML format, but we are required to get the data as unformatted pivot data, but this sentence throws error :
ORA-00936: missing expression
Any resolutions or ideas would be welcomed,
Best Regards
-------------if we can get the result of this to sys_refcursor using execute immediate it will be solved ------------------------
the procedure : PROCEDURE pr_test2 (deneme OUT sys_refcursor)
IS
v_sql NVARCHAR2 (4000) := '';
TYPE v_items IS TABLE OF NVARCHAR2 (30);
v_pivot_items NVARCHAR2 (4000) := '';
BEGIN
FOR i IN (SELECT DISTINCT (item_no) AS items
FROM t_order_line)
LOOP
v_pivot_items := ',''' || i.items || '''' || v_pivot_items;
END LOOP;
v_pivot_items := LTRIM (v_pivot_items, ',');
v_sql :=
'begin
select * from
(SELECT o.ship_to_customer_no, ol.item_no,ol.amount
FROM t_order o, t_order_line ol
WHERE o.NO = ol.order_no and OL.ITEM_NO in (select distinct(item_no) from t_order_line))
pivot --xml
( SUM(amount) FOR item_no IN ('
|| v_pivot_items
|| '));end;';
open DENEME for
select v_sql from dual;
Kayhan YÜKSEL
