create or replace function tab_grp(sum_cols DBMS_TF.COLUMNS_T, t DBMS_TF.Table_t, grp_cols DBMS_TF.COLUMNS_T)
return varchar2 SQL_Macro as
v_sum_list varchar2(4000);
v_grp_list varchar2(4000);
begin
-- concat the SUM column
FOR i IN 1 .. sum_cols.count() LOOP
v_sum_list := v_sum_list || case when i > 1 then ',' end || 'SUM('|| sum_cols(i)|| ') '|| sum_cols(i);
END LOOP;
FOR i IN 1 .. grp_cols.count() LOOP
v_grp_list := v_grp_list || case when i > 1 then ',' end || grp_cols(i);
END LOOP;
return 'select ' || v_grp_list || ', ' || v_sum_list ||' , count(*) cnt from t group by '|| v_grp_list ;
end;
Function created.
create table tab as
select 1 customer_id, 1 product_id, 10 value from dual union all
select 1 customer_id, 1 product_id, 40 value from dual union all
select 1 customer_id, 2 product_id, 20 value from dual union all
select 2 customer_id, 1 product_id, 10 value from dual union all
select 2 customer_id, 1 product_id, 20 value from dual
Table created.
select * from tab_grp (columns(value), tab, columns(customer_id, product_id))
| CUSTOMER_ID | PRODUCT_ID | VALUE | CNT | 2 | 1 | 30 | 2 | 1 | 2 | 20 | 1 | 1 | 1 | 50 | 2 |
|---|
DECLARE
l_clob CLOB;
BEGIN
DBMS_UTILITY.expand_sql_text (
input_sql_text => q'[select * from tab_grp (columns(value), tab, columns(customer_id, product_id))]',
output_sql_text => l_clob );
DBMS_OUTPUT.put_line(l_clob);
END;
Statement processed.
SELECT "A1"."CUSTOMER_ID" "CUSTOMER_ID","A1"."PRODUCT_ID" "PRODUCT_ID","A1"."VALUE" "VALUE","A1"."CNT" "CNT" FROM (SELECT "A3"."CUSTOMER_ID" "CUSTOMER_ID","A3"."PRODUCT_ID" "PRODUCT_ID","A3"."VALUE" "VALUE","A3"."CNT" "CNT" FROM (SELECT "A4"."CUSTOMER_ID" "CUSTOMER_ID","A4"."PRODUCT_ID" "PRODUCT_ID",SUM("A4"."VALUE") "VALUE",COUNT(*) "CNT" FROM (SELECT "A2"."CUSTOMER_ID" "CUSTOMER_ID","A2"."PRODUCT_ID" "PRODUCT_ID","A2"."VALUE" "VALUE" FROM "SQL_NTOIZUWOYDICAPPIYTUZKZBNM"."TAB" "A2") "A4" GROUP BY "A4"."CUSTOMER_ID","A4"."PRODUCT_ID") "A3") "A1"