Rows to columns using PIVOT function (Oracle)

Question

I'm attempting to create a query to transpose rows into columns using the PIVOT function. I have a table in this form (this is just the partial view, the table contains more than 30 columns).

ID      SUBJECT  GRADE
000442  WRI001   C-
000442  PHY104   C
000442  MTH111   B
000442  MTH111   W
000442  MTH111   W
000442  PHY104   W

Expected result:

ID      'WRI001'   'MTH111'   'PHY104'
000442   C-         B,W,W      C,W

Query used:

select * from (
   select ID,
          SUBJECT,GRADE
   from SECOND_YEAR_COMP
             )
pivot 
(
   MAX(GRADE)
   for SUBJECT in
   ('MTH111',
    'WRI001',
    'PHY104')
);

Query Output:

ID      'WRI001'   'MTH111'   'PHY104'
000442   C-         W          W

I know because of MAX(GRADE) I am getting single grade for each subject. Is there any way to get all the grades for the subject (as my expected result give above).

The output you show is not the output from your query. That is because the columns in the output will follow exactly the order in the IN list of the PIVOT clause (unless you have an ORDER BY at the end of your query, which you don't). — user5683823
– user5683823, Commented Oct 16, 2020 at 15:44

GMB · Accepted Answer · 2020-10-16 13:44:32Z

3

You can use listagg() and conditional aggregation:

select id,
    listagg(case when subject = 'WRI001' then grade end) WRI001,
    listagg(case when subject = 'WRI001' then grade end) WRI001,
    listagg(case when subject = 'PHY104' then grade end) PHY104
from second_year_comp
group by id

You can control the order in which the grades appear in the concatenatd string with the within group clause. Say you want to order by grade, then:

select id,
    listagg(case when subject = 'WRI001' then grade end) within group(order by grade) WRI001,
    listagg(case when subject = 'WRI001' then grade end) within group(order by grade) WRI001,
    listagg(case when subject = 'PHY104' then grade end) within group(order by grade) PHY104
from second_year_comp
group by id

answered Oct 16, 2020 at 13:44

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

user5683823 Over a year ago

This is correct - but since the OP was already using the PIVOT operator, why not show the solution using LISTAGG in PIVOT?

Fahmi · Accepted Answer · 2020-10-16 13:47:53Z

1

You can use listagg() for string aggregation and then apply pivot

With cte as
(
SELECT ID, SUBJECT,LISTAGG(GRADE, ',') WITHIN GROUP (ORDER BY grade) AS grade
FROM   tablename
GROUP BY id,SUBJECT
)
select * from (
   select ID,SUBJECT,GRADE from cte)
pivot 
(
   MAX(GRADE)
   for SUBJECT in ('MTH111','WRI001','PHY104')
);

answered Oct 16, 2020 at 13:47

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

user5683823 Over a year ago

Why do the aggregation separate from pivoting? Both are aggregate operations; PIVOT can handle LISTAGG as the aggregate function perfectly well. Not a good answer in my opinion (reflected in downvote).

Marmite Bomber · Accepted Answer · 2020-10-16 14:09:49Z

0

You were nearly done - the only missing point is - you should replace the MAX aggregate function with the LISTAGG function using the full syntax in the PIVOT clause.

Additionaly I adjusted the pivot column names to get nice names (without apostrophes).

See example below:

select * from (
   select ID,
          SUBJECT,GRADE
   from tab
             )
pivot 
(
   LISTAGG(GRADE,',') within group (order by GRADE)
   for SUBJECT in
   ('MTH111' as MTH111,
    'WRI001' as WRI001,
    'PHY104' as PHY104)
)

Result as expected

ID     MTH111     WRI001     PHY104    
------ ---------- ---------- ----------
000442 B,W,W      C-         C,W

edited Oct 16, 2020 at 14:09

answered Oct 16, 2020 at 14:03

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

1 Comment

user5683823 Over a year ago

Almost as expected; the OP shows a different output (which, as I explain in a comment below his question, is not the output from the query he shared with us). Having to do with the column order in the output - the same as the order in the IN list of PIVOT.

Collectives™ on Stack Overflow

Rows to columns using PIVOT function (Oracle)

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related