CODEWITHSUNDEEP
sqlsql-server
Eric Cheung
Eric Cheung

Reputation: 327

Query a table to make each row become 3 rows

I have a table (T1) as follows in SQL server:

id     subject     student1      student2     student3     score1      score2     score3
1      Maths       Peter         Leo          John         11          12         13
2      Phy         Mary          May          Leo          21          22         23

Is it possible to use a query to make the following result:

id    subject   name      score
1     Maths     Peter     11
1     Maths     Leo       12
1     Maths     John      13
2     Phy       Mary      21
2     Phy       May       22
2     Phy       Leo       23

Upvotes: 1

Views: 118

Answers (2)

Frank Conry
Frank Conry

Reputation: 2716

Assuming the table is called classes, you can union it to itself:

(SELECT student1 as student, subject from Classes)
UNION
(SELECT student2 as student, subject from Classes)
UNION
(SELECT student3 as student, subject from Classes)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Although this is an unpivot query, I actually find it easier to do this with explicit logic:

select t1.id, t1.subject,
       (case when n.n = 1 then student1
             when n.n = 2 then student2
             when n.n = 3 then student3
        end) as student,
       (case when n.n = 1 then score1
             when n.n = 2 then score2
             when n.n = 3 then score3
        end) as score
from t1 cross join
     (select 1 as n union all select 2 union all select 3) n;

This should have comparable performance to unpivot. And, it should have better performance than three union all operations (because that requires scanning the table three times).

Upvotes: 5

Related Questions