Oracle SQL to convert related rows to a single row

Question

I have a table which looks like this:

RollNo Name  Subject Score

021    Gokul English 65
021    Gokul French  75
021    Gokul Germany 85
021    Gokul Spanish 95
031    Karth English 51
031    Karth French  61
031    Karth Germany 71

I want to query this table so that related rows are converted to a single row like this:

RollNo Name    English_score   French_score   Germany_score   Spanish_score

021    Gokul        65               75             85              95
031    Karth        51               61             71

How can I accomplish this?

score 2 · Answer 1 · answered Oct 24 '14 at 01:18

use PIVOT

SELECT * FROM
(SELECT "RollNo", "Name", 
"Subject", "score" FROM Table1)  T
PIVOT
( max("score") for "Subject" in
   ('English' as English_score,
    'French'  as French_score,
    'Germany' as Germany_score,
    'Spanish' as Spanishh_score
    )
)

score 2 · Answer 2 · answered Oct 24 '14 at 01:19

Here is a way to do it using joins instead of a pivot:

SELECT t.RollNo, t.Name,
       t2.Score AS English_score,
       t3.Score AS French_score,
       t4.Score AS Germany_score,
       t5.Score AS Spanish_score
FROM 
(
  SELECT DISTINCT RollNo, Name
  FROM table
) AS t
LEFT JOIN table t2 ON t.RollNo = t2.RollNo AND t2.Subject = 'English'
LEFT JOIN table t3 ON t.RollNo = t3.RollNo AND t3.Subject = 'French'
LEFT JOIN table t4 ON t.RollNo = t4.RollNo AND t4.Subject = 'Germany'
LEFT JOIN table t5 ON t.RollNo = t5.RollNo AND t5.Subject = 'Spanish'

jpw · Answer 3 · 2014-10-24T01:26:59.330

2

If you're using Oracle Database 11g or later you can use pivot. Try this:

SELECT * FROM (
  SELECT RollNo, Name, Subject, Score
  FROM table1
)
PIVOT (MAX(Score) AS Score FOR (Subject) 
 IN (
   'English' AS English, 
   'French' AS French, 
   'Germany' AS Germany, 
   'Spanish' AS Spanish
   )
 );

Sample SQL Fiddle

edited Oct 24 '14 at 01:26

answered Oct 24 '14 at 01:19

jpw

44,361
6
66
86

score 0 · Answer 4 · edited Jan 24 '17 at 14:40

Try this

select 
    rollno, name, subject, 
    english_score, french_score, german_source, spanish_score
from
    (select 
         RollNo, name, subject,
         decode(subject, "English", score, "") English_score,
         decode(subject, "French", score, "") French_score,
         decode(subject, "Germany", score, "") German_score,
         decode(subject, "Spanish", score, "") Spanish_score,
         count(*)
     from 
         tbl
     group by 
         RollNo, name, subject,
         decode(subject, "English", score, "") English_score,
         decode(subject, "French", score, "") French_score,
         decode(subject, "Germany", score, "") German_score,
         decode(subject, "Spanish", score, "") Spanish_score)) tbl2

There is an option in Oracle to not display a column. I think it's "noprint" I just don't remember the syntax. You'd put it next to the count(*):

Select 
    RollNo, name, subject,
    decode(subject, "English", score, "") English_score,
    decode(subject, "French", score, "") French_score,
    decode(subject, "Germany", score, "") German_score,
    decode(subject, "Spanish", score, "") Spanish_score,
    count(*) noprint
from 
    tbl
group by 
    RollNo, name, subject,
    decode(subject, "English", score, "") English_score,
    decode(subject, "French", score, "") French_score,
    decode(subject, "Germany", score, "") German_score,
    decode(subject, "Spanish", score, "") Spanish_score

Thanks Friends.. We can use XMLAGG(xmlelement(E , score || ' ')) followed by Group by command to achieve the desired results. — GKN, Dec 17 '14 at 01:04

Oracle SQL to convert related rows to a single row

4 Answers4

Related