Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, raj.

Asked: February 22, 2017 - 11:02 am UTC

Last updated: August 23, 2017 - 4:50 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have a table with data as below

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

now my requirement is to get the output as below. Get the count of the branchname and transpose them into columns

100 103 104 105 106
2 1 3 2 1

I met the requirement by using the below sql

WITH TAB AS (
SELECT BRANCHNAME, COUNT(*) cnt FROM BRANCH GROUP BY BRANCHNAME)

select max(col1),max(col2), max(col3), max(col4), max(col5) from (
SELECT CASE WHEN BRANCHNAME = '100' THEN CNT END as col1,
CASE WHEN BRANCHNAME = '105' THEN CNT END AS COL2,
CASE WHEN BRANCHNAME = '103' THEN CNT END AS COL3,
CASE WHEN BRANCHNAME = '106' THEN CNT END AS COL4,
CASE WHEN BRANCHNAME = '104' THEN CNT END AS COL5,
RANK() OVER (PARTITION BY BRANCHNAME ORDER BY CNT) RN
FROM TAB)
group by rn

But how to do that dynamically rather than hardcoding in case statements. Please help me

Thanks
Raj

and we said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great example but broken link to the Oracle blog entry

Jennifer Cullen, August 23, 2017 - 3:29 pm UTC

The example given is great, but I would like to read more about how this works. The link to the Oracle blog post is broken, can it be corrected?
Chris Saxon
August 23, 2017 - 4:50 pm UTC

Thanks for letting us know, I've updated it now.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.