Hello everyone,
I want to create a pivot report with dynamic columns.
It should look like this:
SELECT *
FROM
*(SELECT position, att_name, pro_name, value*
FROM cat_attributes)
PIVOT (
max (value)
FOR pro_name
IN ('MS 3', 'MS 4')
*)*
order by position;
static colums work well.
I know, that I must not use a sql query in "IN"- expression. pivot XML would be no option.
So I tried to solve that issue with a curser:
DECLARE
l_rc SYS_REFCURSOR;
l_dynamic_query VARCHAR2(32000);
BEGIN
FOR cc IN (SELECT distinct pro_name from cat_attributes) LOOP
-- build dynamic query here
END LOOP;
OPEN l_rc FOR '
SELECT *
FROM (SELECT position,att_name, pro_name,value
FROM cat_attributes
*)*
PIVOT ( MAX (value) --<-- pivot_clause
FOR pro_name --<-- pivot_for_clause
IN (' || l_dynamic_query || ')
*)';*
-- process l_rc (LOOP..FETCH..CLOSE)
END;
But I got some errors. Maybe someone can help me
errors:
+1 error has occurred+
ORA-06550: line 8, column 4: PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit
for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier>
+<a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe+
purge