go_pivot

CREATE OR REPLACE FUNCTION go_pivot RETURN VARCHAR2
  IS
      l_query LONG := 'select col2';
  BEGIN
      FOR x IN (SELECT DISTINCT col1 FROM test ORDER BY col1)
      LOOP
          l_query := l_query ||
             REPLACE( q'|, sum(decode(col1,'$X$',v)) $X$|',
                      '$X$',
                     x.col1);
     END LOOP;

     l_query := l_query || ' from test group by col2';

    RETURN l_query;
 END;