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