rom (
                   select God, Kod_sotr, /*weeks*/
to_char(trunc(to_date(god,'yyyy'),'y')+(cast(ltrim(weeks,'N') as number)-1)*7,'dd.mm') Weeks_D, 
                   Vacation, cast(ltrim(weeks,'N')as number) Week_Number from t2

create or replace FUNCTION get_week_list_prob(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''б'') THEN '|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon')||'||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
RETURN RTRIM(l_res);
END;

EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;

println

create or replace FUNCTION get_week_list_prob(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''б'') THEN '|| to_char( trunc (to_date(i_year||'0101', 'yyyymmdd') 
+ ((LTRIM(column_name,'N'))-1)*7, 'ww'), 'dd.mon')||'||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
  dbms_output.enable;
  dbms_output.put_line('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year') ;
--EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
--RETURN RTRIM(l_res);
END;

SELECT t.god, u.FIO, (regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '^([[:digit:]]+)(,)([[:digit:]]+)(;)') || 
regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '([[:digit:]]+)(,)([[:digit:]]+)(;)$')) as за_свой_счёт FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

println

--RETURN RTRIM(l_res);

create or replace FUNCTION get_week_list_prob(i_code IN INTEGER, i_year in integer) RETURN VARCHAR2 IS
  l_list  VARCHAR2(32767);
  l_res   VARCHAR2(4000);
BEGIN
SELECT listagg('CASE WHEN '||column_name||' IN(''б'') THEN '|| to_char(trunc(to_date(i_year,'yyyy'),'y')+((LTRIM(column_name, 'N'))-1)*7,'dd.mon')||'||''; '' ELSE NULL END ' ,'||') 
WITHIN GROUP(ORDER BY 1)
  INTO  l_list
  FROM  user_tab_columns
  WHERE TABLE_NAME = 'GRAFIK' 
  AND column_name LIKE 'N%';
 dbms_output.enable;
dbms_output.put_line('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=' ||i_code||' AND god=' ||i_year);
l_res:=('SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=' ||i_code||' AND god=' ||i_year);
return l_res;
---EXECUTE IMMEDIATE 'SELECT '||l_list||' FROM GRAFIK WHERE kod_sotr=:A AND god=:year' INTO l_res USING i_code,i_year;
---RETURN RTRIM(l_res);
END;

SELECT t.god, u.FIO, (regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '^([[:digit:]]+)(,)([[:digit:]]+)(;)') || 
regexp_substr (get_week_list_prob(t.kod_sotr,t.god), '([[:digit:]]+)(,)([[:digit:]]+)(;)$')) as за_свой_счёт FROM GRAFIK t, USERS u 
where t.KOD_SOTR = u.KOD_SOTR;

select get_week_list_prob(0,0) s from dual

SELECT CASE WHEN N01 IN('б') THEN 01.янв||'; ' ELSE NULL END ||CASE WHEN N02 IN('б') THEN 08.янв||'; ' ELSE NULL END |