with t as (
select 123456 DRAW_KEY, 1 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 2 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 3 WINCAT_NUM,0 WIN_AMOUNT,0 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 4 WINCAT_NUM,30000 WIN_AMOUNT,12 WIN_COUNT
from dual
union all
select 123456 DRAW_KEY, 5 WINCAT_NUM,6000 WIN_AMOUNT,73 WIN_COUNT
from dual
)
select *
from t

CREATE TYPE l_result AS OBJECT (
 DRAW_KEY number,
  WINCAT_NUM  number,
  WIN_AMOUNT number,
  WIN_COUNT number
);
/

CREATE TYPE table_pip IS TABLE OF l_result;


CREATE OR REPLACE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS
v_query clob;
v_cols clob;
BEGIN
select listagg(wincat_num ,', ') within group (order by wincat_num)
into v_cols
from customers1
where draw_key = 123456; --v_draw_key!
v_query := '
SELECT * FROM
(
select draw_key,wincat_num,win_amount,win_count
from customers1
where draw_key = 123456
)
PIVOT
(
sum(win_count) as win_count,sum(win_amount) as win_amount
FOR wincat_num IN ('||v_cols||')
)';
EXECUTE IMMEDIATE v_query into l_result;

pipe row(l_result);
END;

CREATE OR REPLACE FUNCTION MyFunction1(v_draw_key in number) RETURN table_pip Pipelined IS