CODEWITHSUNDEEP
sqloracle-databasepivot
BetaDrone
BetaDrone

Reputation: 1

Cannot not Convert Rows to Columns From SubQuery

I'm currently having real issues trying to convert rows into columns from a sub query. I need to make a simple table that calculates a number of attributes per agent (for simplicity sake, I've only included one aggregate field for the moment) and then transpose the rows to columns.

Essentially, all I want is to take the output of the subquery and make the values within Agent_Name to become the Columns and the Outcome_1 field to become the row then Outcome_2 ..3 and so on; however, my attempts so far only list the agent names but doesn't list the OUTCOME_1 or any values.

Using Oracle Database

Current Code Attempt

Select *
from
(Select AGENT_NAME, OUTCOME_1 from
(select AGENT_NAME, 
SUM(CASE when CLOSING_REASON = 6 then 1 else 0 end) as OUTCOME_1
from all_cases
join
users
on all_cases.user_ID = USers.ID
where Group_ID = 14
and Start_time > to_DATE ('01/04/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
group by FULL_NAME)T1)
Pivot(MAX(OUTCOME_1) for AGENT_NAME in ('USER1','User2','User3','User4'))

Upvotes: 0

Views: 178

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Just use conditional aggregation:

select u.agent_name,
       sum(case when CLOSING_REASON = 6 then 1 else 0 end) as OUTCOME_1,
       . . . 
from all_cases ac join
     users
     on ac.user_ID = u.ID
where Start_time >= date '2020-04-01'
group by u.agent_name;

If you want the agents pivoted and outcomes on rows, the idea is the same, just the details change:

select closing_reason,
       sum(case when agent_name = 'a' then 1 else 0 end) as agent_a,
       sum(case when agent_name = 'b' then 1 else 0 end) as agent_b,
       . . . 
from all_cases ac join
     users
     on ac.user_ID = u.ID
where Start_time >= date '2020-04-01'
group by u.agent_name;

Upvotes: 1

Related Questions