oracle plsql select pivot without dynamic sql to group by
Posted
by
kayhan yüksel
on Stack Overflow
See other posts from Stack Overflow
or by kayhan yüksel
Published on 2011-01-15T11:33:31Z
Indexed on
2011/01/15
15:53 UTC
Read the original article
Hit count: 255
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
© Stack Overflow or respective owner
