Need help- Using pivot function for to get total executions by users per exchange

Question

I am working on a report where I have to extract total quantity executed by traders per exchange, I am am able to get the data in below format eg-

username Exchange totalsharestraded

A        BSE      1000000
A        NSE      2000000
B        BSE      30000
B        NSE      4000000

But the number of exchanges are more than 12 and the number of users are 60 and the reporting requirement is to present the data in below format -

Username   NSE       BSE 

A          2000000   1000000
B          4000000   30000

Any suggestions how to use pivot clause for above requirements?

Answer 1

If you strictly need to use pivot function, You may try -

SELECT *
  FROM (SELECT username, Exchange, totalsharestraded
          FROM YOUR_TABLE)
 PIVOT (SUM(totalsharestraded) FOR Exchange IN ('NSE' AS NSE, 'BSE' AS BSE));

Answer 2

A simple option is to conditionally sum those numbers. Sample data in lines #1 - 7; query you might be interested in begins at line #8.

SQL> with test (username, exchange, totalsharestraded) as
  2    -- sample data
  3    (select 'A', 'BSE', 1000000 from dual union all
  4     select 'A', 'NSE', 2000000 from dual union all
  5     select 'B', 'BSE',   30000 from dual union all
  6     select 'B', 'NSE', 4000000 from dual
  7    )
  8  select username,
  9    sum(case when exchange = 'BSE' then totalsharestraded else 0 end) as bse,
 10    sum(case when exchange = 'NSE' then totalsharestraded else 0 end) as nse
 11  from test
 12  group by username;

USERNAME          BSE        NSE
---------- ---------- ----------
A             1000000    2000000
B               30000    4000000

SQL>