Sorting?

[edustreet]

Hi,

Table:
Name Subject Grade
Jason 1 A
Jason 2 A
Jason 3 A
Elvis 1 A
Elvis 2 A
Elvis 3 B
John 1 A
John 2 C
John 3 B

Output:
Name 1 2 3
Jason A A A
Elvis A A B
John A C B

Is that possible to get the result from the above? For what I want is the student who get more distinction subject will be at the top. It is not like this "select ... from ... order by 1,2,3"

Any suggestion and help will be more appreciate. Thanks

Calvin

[skhanal]

See if this works.

select name, max('1'), max('2'), max('3')
from (
select name, grade as '1',null as '2', null as '3'
from table
where subject='1'
union
select name, null as '1',grade as '2', null as '3'
from table
where subject='2'
union
select name, null as '1',null as '2', grade as '3'
from table
where subject='3') as A
group by name

[edustreet]

Thanks for your code, but output was incorrect. Get this output:

Expr1 Expr2 Expr3
Elvis 1 2 3
Jason 1 2 3
John 1 2 3

[edustreet]

Finally, change code to:

SELECT Name, MIN(S1) AS Subject1, MIN(S2) AS Subject2, MIN(S3) AS Subject3
FROM (SELECT Name, Grade AS S1, NULL AS S2, NULL AS S3
FROM Report
WHERE (Subject = '1')
UNION
SELECT Name, NULL AS S1, Grade AS S2, NULL AS S3
FROM Report AS Report_2
WHERE (Subject = '2')
UNION
SELECT Name, NULL AS S1, NULL AS S2, Grade AS S3
FROM Report AS Report_1
WHERE (Subject = '3')) AS A
GROUP BY Name


Get this output:
Name Subject1 Subject2 Subject3
Elvis A A B
Jason A A A
John A C B


* The output order was incorrect, Jason should be in the first row!!