CODEWITHSUNDEEP
sqloracle-databasepivotcrosstab
Fearthegrimp
Fearthegrimp

Reputation: 11

Oracle SQL Crosstab with Sum

I'm fairly new to Oracle SQL and have been tasked with creating a crosstab report using SQL. I have a single source table with a simple structure similar to this:

TABLE STRUCTURE

I'm trying to crosstab the results to show the total staff per state as follows:

DESIRED RESULTS

Ideally I'd like to dynamically cater for offices opening in new states.

I've been looking into PIVOTS but can't seem to get my head around it. Any guidance would be gratefully received.

Thank you.

Upvotes: 1

Views: 2070

Answers (2)

LukStorms
LukStorms

Reputation: 29677

In a PIVOT you can start from a source sub-query.
Then you define what field to aggregate for which titles in another field. 

SELECT *
FROM
(
  SELECT Company, State, Staff
  FROM YourCompanyStaffTable
  WHERE State IN ('Illinois', 'Texas', 'Tennessee', 'Missouri', 'Kansas', 'Indiana')
) src
PIVOT (
  SUM(Staff)
  FOR State IN (
    'Illinois' as Illinois, 
    'Texas' as Texas, 
    'Tennessee' as Tennessee, 
    'Missouri' as Missouri, 
    'Kansas' as Kansas, 
    'Indiana' as Indiana
  )
) pvt
ORDER BY Company

In this query, the new column names are generated from the "State" column.
Note that in the source query there's also a limit on those names.
That's just for efficiency reasons. (less data to pull from the table)

And it'll group the results by the source fields that aren't used in the PIVOT declaration.
In this case it automatically groups on the "Company" column.

So it sums the total "Staff" for each "State" per "Company".

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use case when

select company, sum(case when state='Illinois' then staff else 0 end) as  Illinois,
 sum(case when state='Texas' then staff else 0 end) as  Texas,
 sum(case when state='Tennessee' then staff else 0 end) as  Tennessee,
 sum(case when state='Missouri' then staff else 0 end) as  Missouri,
 sum(case when state='Kansas' then staff else 0 end) as  kansas,
sum(case when state='Indiana' then staff else 0 end) as  Indiana from t
group by company

Upvotes: 1

Related Questions