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

What are you doing with the results of the query? If a client application is going to consume the result set then it will generally need/want to know how many columns to expect back too, and it might be more appropriate for the client to get the aggregated data as multiple rows (as MTO showed) and do the pivot itself. — Alex Poole
– Alex Poole, Commented Feb 16, 2017 at 11:29
Output it in rows and then use excel's TRANSPOSE function to swap it to columns. — MT0
– MT0, Commented Feb 16, 2017 at 12:06
It's not possible in plain SQL. The number of columns of a SQL needs to be know to the database before starting the execution of the query. — user330315
– user330315, Commented Feb 16, 2017 at 12:12
@seyxsultan: that is not "plain SQL" that is some very advanced PL/SQL programming — user330315
– user330315, Commented Feb 16, 2017 at 12:28
@seyxsultan The query result that you have posted is missing column '106', looks like '103' is printed twice. — Tom J Muthirenthi
– Tom J Muthirenthi, Commented Feb 23, 2017 at 7:24

Jon Heller · Accepted Answer · 2017-02-19 07:56:54Z

6

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

edited Feb 19, 2017 at 7:56

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

answered Feb 16, 2017 at 11:18

Kamil Ibadov

7103 gold badges21 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user7479343 Over a year ago

It seems ok, but can you help write exact code what I need?

Kamil Ibadov Over a year ago

It will take more time, when I will have free time, I will do for you :) So lazy developer ;)

mikcutu · Accepted Answer · 2017-02-16 13:15:44Z

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;

answered Feb 16, 2017 at 13:15

mikcutu

1,1022 gold badges19 silver badges40 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:09:33Z

2

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.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Feb 16, 2017 at 10:23

MT0

173k12 gold badges70 silver badges136 bronze badges

3 Comments

user7479343 Over a year ago

I don't understood, you mean it's not possible to do that without knowing exact names of Branches? Or If possible using dynamic pivot can you help write query?

MT0 Over a year ago

@seyxsultan It is not possible to do it using pure SQL - you will need to generate some dynamic SQL and invoke it in a PL/SQL block/function. There are multiple possible methods in the question I linked to but, my advice would be that, you should not be going down that route as it is a performance and maintenance sink-hole.

MT0 Over a year ago

@seyxsultan Updated with a very simple solution to use excel to TRANSPOSE the SQL row data to excel column data.

javasenior · Accepted Answer · 2017-02-21 09:26:59Z

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

Tom J Muthirenthi · Accepted Answer · 2017-02-22 18:03:14Z

2

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.

answered Feb 22, 2017 at 18:03

Tom J Muthirenthi

3,3408 gold badges49 silver badges68 bronze badges

1 Comment

Tom J Muthirenthi Over a year ago

I appreciate any help in getting the 'parsing xml' step done.

Aya Aboud · Accepted Answer · 2017-02-19 10:07:29Z

-1

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.

answered Feb 19, 2017 at 10:07

Aya Aboud

3792 gold badges4 silver badges16 bronze badges

Collectives™ on Stack Overflow

dynamic columns in oracle using sql

6 Answers 6

2 Comments

Comments

3 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related