dynamic columns in oracle using sql

I think it is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function will use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

• Dynamic SQL Pivoting
• The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

• Method4. After downloading and installing the open source PL/SQL code, here is a complete implementation:

  --Create sample table.create table branch_data asselect '100' BranchName,'1001010' CustomerNo from dual   UNION ALL select '100' BranchName,'1001011' CustomerNo from dual   UNION ALL select '103' BranchName,'1001012' CustomerNo from dual   UNION ALL select '104' BranchName,'1001013' CustomerNo from dual   UNION ALL select '104' BranchName,'1001014' CustomerNo from dual   UNION ALL select '104' BranchName,'1001015' CustomerNo from dual   UNION ALL select '105' BranchName,'1001016' CustomerNo from dual   UNION ALL select '105' BranchName,'1001017' CustomerNo from dual   UNION ALL select '106' BranchName,'1001018' CustomerNo from dual;--Create a dynamic pivot in SQL.select *from table(method4.dynamic_query(    q'[        --Create a select statement        select            --The SELECT:            'select'||chr(10)||            --The column list:            listagg(                replace(q'!sum(case when BranchName = '#BRANCH_NAME#' then 1 else 0 end) "#BRANCH_NAME#"!', '#BRANCH_NAME#', BranchName)                , ','||chr(10)) within group (order by BranchName)||chr(10)||            --The FROM:            'from branch_data' v_sql        from        (            --Distinct BranchNames.            select distinct BranchName            from branch_data        )    ]'));

If you just want to report the results somewhere, you may use a cursor for the select statement:

select branchname, count(*) from test group by branchname order by branchname asc;

Looping through the cursor you may get your values.

here is my sample:

declare  v_b varchar2(1000);  v_t varchar2(1000);begin  for i in (select branchname, count(*) total from test group by branchname order by branchname asc)  loop      v_b := v_b || i.branchname || ' ';      v_t := v_t || i.total || '   ';       end loop;  dbms_output.put_line(v_b);  dbms_output.put_line(v_t);end;

This will get it in rows (rather than columns):

SELECT branchname,       COUNT( DISTINCT customerno ) AS customersFROM   your_tableGROUP BY branchname;

(Note: you can omit the DISTINCT keyword if there will never be repeats of the branchname, customerno pair.)

Without knowing what the branch names are you are could only do a dynamic pivot.

It would be much simpler to take the output of the above query (in row format) and transpose it in whatever front-end you are using to access the database.

From comments:

I need a report in this format, and don't want write some application , wants to do with sql for easily export to excell in such format

No, you don't need it in column format in SQL. You can put it into excel in row format and then use excel's TRANSPOSE function to convert it (very simply) to columns without having to implement a complicated dynamic SQL solution.