Two tables and Rows into Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Two tables and Rows into Columns

Author

Topic

alvin_deang
Starting Member

2 Posts

Posted - 2008-12-04 : 19:49:04

Please help with this

1st Table - tblStudent
ID Name
1 Boy1
2 Boy2

2nd Table - tblGrade
ID Grade Term
1 86 Prelim
2 89 Prelim
1 83 Midterm
2 85 Midterm
1 90 Pre-Final
2 89 Pre-Final
1 86 Final
2 89 Final

Output should be

ID Name Prelim Midterm Pre-Final Final
1 Boy1 86 83 90 86
2 Boy2 89 85 89 89

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 20:05:17

select t.ID,t.[Name],
max(case when Term = 'Prelim' then Grade else null end ) as Prelim,
max(case when Term = 'MidTerm' then Grade else null end ) as MidTerm,
max(case when Term = 'Pre-Final' then Grade else null end ) as [Pre-Final],
max(case when Term = 'Final' then Grade else null end ) as Final
from table1 t inner join table2 m
on t.ID =m.ID
Group by t.ID,t.[Name]

alvin_deang
Starting Member

2 Posts

Posted - 2008-12-04 : 20:28:38

Thanks alot

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 20:30:19

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources