CODEWITHSUNDEEP
sqlstringoracle-databasepivot
AJ Zacharia
AJ Zacharia

Reputation: 43

Rows to columns using PIVOT function (Oracle)

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).

Upvotes: 4

Views: 8824

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21073

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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')
);

Upvotes: 1

GMB
GMB

Reputation: 222462

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

Upvotes: 3

Related Questions