dynamic columns in oracle using sql

Question

I have following example of table. Thera can be unlimited branch and customers. I need group this branches and count their customers, then show it's with different columns.

BRANCHNAME  CUSTOMERNO
100         1001010
100         1001011
103         1001012
104         1001013
104         1001014
104         1001015
105         1001016
105         1001017
106         1001018

Note that there can be unlimited branch and customers, the query must work not only this case.

In this case the accepted result is:

100 103 104 105 106
 2   1   3   2   1

Example SQL DATA

    select '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   

Answer 1

What about this solution. Without no table creation, just set the v_sql parameter.

SET SERVEROUTPUT ON SIZE 100000

DECLARE
   v_cursor    sys_refcursor;

   CURSOR get_columns
   IS
      SELECT EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE
        FROM (SELECT *
                FROM TABLE (XMLSEQUENCE (v_cursor))) t1,
             TABLE (XMLSEQUENCE (EXTRACT (t1.COLUMN_VALUE, '/ROW/node()'))) t2;

   v_column    VARCHAR2 (1000);
   v_value     VARCHAR2 (1000);
   v_counter   NUMBER (3)      := 0;
   v_sql       VARCHAR2 (4000);
BEGIN
   v_sql :=
         'SELECT   branchname, COUNT (DISTINCT customerno) AS customers'
      || ' FROM (SELECT 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)'
      || ' GROUP BY branchname';

   OPEN v_cursor FOR v_sql;

   FOR v_record IN get_columns
   LOOP
      IF v_counter = 0
      THEN
         v_column := v_column || v_record.VALUE || ' ';
         v_counter := 1;
      ELSIF v_counter = 1
      THEN
         v_value := v_value || v_record.VALUE || '   ';
         v_counter := 0;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (v_column);
   DBMS_OUTPUT.put_line (v_value);
END;
/

And the output is

100 105 104 103 106 
2   2   3   1   1  

Answer 2

You can use this selection:

SELECT branchname, count(*) 
FROM test 
GROUP BY branchname

In general it is not professional to use selection for every number in branchname.

Answer 3

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 as
  select '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
          )
      ]'
  ));
  

Answer 4

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;

Answer 5

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

SELECT branchname,
       COUNT( DISTINCT customerno ) AS customers
FROM   your_table
GROUP 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.

Answer 6

with src as
(select '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 )
SELECT * FROM
(select BranchName from src)
PIVOT XML 
(COUNT(*) FOR (BranchName) 
IN 
(SELECT DISTINCT BranchName FROM SRC))

This query gives the output in xml format. The whole xml data will be contained in the field that the query results(The query has only single row-sinlge column output). The next step is to parse the xml data and display it in tabular form.