Dynamic columns in oracle using sql

Question

I have the following example table. Tera can be unlimited affiliate and customers. I need to group these branches and count their customers, and then show them in different columns.

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

Please note that there is an unlimited branch and clients, the request should work not only in this case.

In this case, the accepted result:

 100 103 104 105 106 2 1 3 2 1 

SQL DATA Example

  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

I think it’s possible, albeit quite difficult, to write a pipelined table function that returns the structure of variables . The pipeline table function will use the Oracle Data Cartridge interface and magic like AnyDataSet to return the dynamic structure at run time. You can then use this in subsequent SQL operations as if it were a table, i.e.

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

A few more links that discuss the same sample implementation

• Dynamic SQL rotation
• Implementing the Interface> in the Oracle Data Cartridge Cartridge Developer Guide

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

   --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 2

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

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

By scrolling, you can get your values.

here is my example:

 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 3

This will get it in rows (not in 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 a repetition of a pair of branchname , customerno .)

Not knowing which branch names you can use only a dynamic core .

It would be much easier to display the result of the above query (in string format) and transfer it to any interface that you use to access the database.

From the comments:

I need a report in this format, and I do not want to write any application, I want to do with sql for easy export to excell in this format

No, you do not need this in column format in SQL. You can put it in excel format in a row and then use the excel TRANSPOSE function to convert it (very simply) to columns without having to implement a complex dynamic SQL solution.

Answer 4

How about this solution. Without creating a table, 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 conclusion

 100 105 104 103 106 2 2 3 1 1 

Answer 5

 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 displays the result in xml format. All XML data will be contained in the field in which the query results will be displayed (the query has only one column output of the sinlge string). The next step is to parse the xml data and display it in tabular form.

Answer 6

You can use this option:

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

In general, it is not professional to use the selection for each number in branchname .