If you want to convert rows to columns, you really should use pivot!
This helps, but dynamic columns are still a challenge. You can do it with XML pivoting:
create table t (
BRANCHNAME int,
CUSTOMERNUM int
);
insert into t values (100, 1001010);
insert into t values (100, 1001011);
insert into t values (103, 1001012);
insert into t values (104, 1001013);
insert into t values (104, 1001014);
insert into t values (104, 1001015);
insert into t values (105, 1001016);
insert into t values (105, 1001017);
insert into t values (106, 1001018);
set long 100000
select * from (
select BRANCHNAME from t
)
pivot xml (count(*) for branchname in (any));
<?xml version="1.0" encoding="UTF-8"?>
<PivotSet>
<item>
<column name="BRANCHNAME">100</column>
<column name="COUNT(*)">2</column>
</item>
<item>
<column name="BRANCHNAME">103</column>
<column name="COUNT(*)">1</column>
</item>
<item>
<column name="BRANCHNAME">104</column>
<column name="COUNT(*)">3</column>
</item>
<item>
<column name="BRANCHNAME">105</column>
<column name="COUNT(*)">2</column>
</item>
<item>
<column name="BRANCHNAME">106</column>
<column name="COUNT(*)">1</column>
</item>
</PivotSet>
But now you've got to parse the XML...
Alternatively you can use dynamic SQL. With this you can use listagg of or similar to create the IN clause. Then add it to your statement, e.g.:
declare
sql_stmt varchar2(4000);
pivot_clause varchar2(4000);
begin
select listagg('''' || BRANCHNAME || ''' as "' || BRANCHNAME || '"', ',') within group (order by BRANCHNAME)
into pivot_clause
from (select distinct BRANCHNAME from t);
sql_stmt := 'select * from (select branchname from t)
pivot (count(*) for branchname in (' || pivot_clause || '))';
execute immediate sql_stmt;
end;
/
Finally there's Anton Scheffer's custom types solution:
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ You can read more about pivoting in general and dynamic pivoting specifically at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot