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, Feb 16 '17 at 11:29
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 — , Feb 16 '17 at 11:33
Output it in rows and then use excel's [`TRANSPOSE`](https://support.office.com/en-gb/article/TRANSPOSE-function-ed039415-ed8a-4a81-93e9-4b6dfac76027) function to swap it to columns. — MT0, Feb 16 '17 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. — , Feb 16 '17 at 12:12
@a_horse_with_no_name Kamil Ibadov added solution for unknown type and count of columns (Oracle Data Cartridge) — , Feb 16 '17 at 12:15
@seyxsultan: that is **not** "plain SQL" that is some very advanced PL/SQL programming — , Feb 16 '17 at 12:28
From comments, the OP does not appear to want any other solution other than an SQL solution which means using dynamic SQL - I would suggest that this question is then a duplicate of [Dynamic pivot in oracle sql](http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) — MT0, Feb 16 '17 at 12:53
@seyxsultan The query result that you have posted is missing column '106', looks like '103' is printed twice. — Tom J Muthirenthi, Feb 23 '17 at 07:24

score 6 · Accepted Answer · edited Feb 19 '17 at 07:56

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

It will take more time, when I will have free time, I will do for you :) So lazy developer ;) — Kamil Ibadov, Feb 16 '17 at 11:22

score 4 · Answer 2 · answered Feb 16 '17 at 13:15

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;

score 2 · Answer 3 · edited May 23 '17 at 12:09

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 '17 at 12:09

Community

1
1

answered Feb 16 '17 at 10:23

MT0

143,790
11
59
117

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? – Feb 16 '17 at 10:34
@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 Feb 16 '17 at 10:48
1

@seyxsultan Updated with a very simple solution to use excel to [`TRANSPOSE`](https://support.office.com/en-gb/article/TRANSPOSE-function-ed039415-ed8a-4a81-93e9-4b6dfac76027) the SQL row data to excel column data. – MT0 Feb 16 '17 at 12:47

score 2 · Answer 4 · answered Feb 21 '17 at 09:26

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

score 2 · Answer 5 · answered Feb 22 '17 at 18:03

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.

I appreciate any help in getting the 'parsing xml' step done. — Tom J Muthirenthi, Feb 22 '17 at 18:12

score -1 · Answer 6 · answered Feb 19 '17 at 10:07

-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 '17 at 10:07

Aya Aboud

371
2
4
16

dynamic columns in oracle using sql

6 Answers6

Linked