Columns To Rows

alyeng2000 · March 9th, 2005, 04:02 PM

I have A Table Like

SName Material Score Month year
a1 m1 20 1 2000
a2 m1 40 1 2000
a3 m1 60 1 2000
a4 m1 30 1 2000
a1 m2 20 1 2000
a2 m2 10 1 2000
a3 m2 60 1 2000
a4 m2 80 1 2000
a1 m3 70 1 2000
a2 m3 40 1 2000
a3 m3 20 1 2000
a4 m3 80 1 2000

i need to get output like
all Students Best Material Scores And Each Type Per Row

a1 a2 a3 a4
m3 m1 m1 m2
70 40 60 80

Any Could Help

Ahmed Ali
Software Developer

jbenson001 · March 10th, 2005, 01:29 AM

Try:
   Select
      SName, Material, Max(Score)
   From
      <table>
   Group By SName, Material

alyeng2000 · March 11th, 2005, 03:08 PM

What I need To Done Is Like that But I Need Extra Shape i need In Row Shape
All Descriped In Following Script

select sname
,(select top 1 inTab.mat
    from #StScore as inTab
    where inTab.sname=#StScore.sname
    order by inTab.score desc)
,max(score) as Sc
from #StScore
group by sname

The Result Is
a1 m3 90
a2 m3 70
a3 m1 50
a4 m1 60

What I need
a1 a2 a3 a4
m3 m3 m1 m1
90 70 50 60

Ahmed Ali
Software Developer