Oracle 11G R2 SQL rows to columns

Question

I have a table of bank staff information that looks like this:

branchNumber    Position    firstName    lastName    staffNumber
------------    --------    ---------    --------    -----------
25              Manager     john         doe         11111
25              Secretary   robert       paulson     11112
25              Secretary   cindy        lu          11113
66              Manager     tim          timson      22223
66              Manager     jacob        jacobson    22224
66              Secretary   henry        henryson    22225
66              Supervisor  paul         paulerton   22226

I am actually done with this, but I completed the assignment using SQL common table expressions, and I can't use them in this project, I need them in this format.

branchNumber    numOfManagers    numOfSecretaries    numOfSupervisors    totalEmployees
------------    -------------    ----------------    ----------------    --------------
25                    1                 2                   0                   3
66                    2                 1                   1                   4

My issue is getting multiple columns with information from a row, I have this so far,

SELECT branchNumber, COUNT(*) AS numOfManagers
FROM Staff
WHERE position = 'Manager'
GROUP BY branchNumber, Position;

This outputs the correct information for numOfManagers, but making the next three columns eludes me without using CTE's. I tried sub selects too, with no luck. Anybody have any ideas?

Taryn · Accepted Answer · 2012-09-28 19:52:56Z

6

You can use something like this:

select branchnumber,
  sum(case when Position ='Manager' then 1 else 0 end) numofManagers,
  sum(case when Position ='Secretary' then 1 else 0 end) numofSecretaries,
  sum(case when Position ='Supervisor' then 1 else 0 end) numofSupervisors,
  count(*) totalEmployees
from yourtable
group by branchnumber

See SQL Fiddle with Demo

Or you can use the PIVOT function:

select branchnumber,
  'Manager', 'Secretary', 'Supervisor',
  TotalEmployees
from
(
  select t1.branchnumber,
    t1.position,
    t2.TotalEmployees
  from yourtable t1
  inner join
  (
    select branchnumber, count(*) TotalEmployees
    from yourtable
    group by branchnumber
  ) t2
    on t1.branchnumber = t2.branchnumber
) x
pivot
(
  count(position)
  for position in ('Manager', 'Secretary', 'Supervisor')
) p;

See SQL Fiddle with Demo

edited Sep 28, 2012 at 19:52

answered Sep 28, 2012 at 19:46

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

1 Comment

praiseHellRaiseDale Over a year ago

Very nice, thank you very much, the "case when" part is exactly what I was looking for, and very intuitive. Thanks for the help, and the PIVOT is incredibly useful as well.

Collectives™ on Stack Overflow

Oracle 11G R2 SQL rows to columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest