select multiple columns from same column in a tabl

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

Transact-SQL (2000)

select multiple columns from same column in a tabl

Author

Topic

mayhem
Starting Member

11 Posts

Posted - 2004-02-27 : 21:26:47

Let's say I have a table with the fields Student_Id, Test_Id, Test_Marks. I want to do a select so that I will return some Student_Id, with their respective Test_Marks for one test, say Test_Id=70, in one column. Then, I want another column where I get the test_marks for another test, say test_Id = 71. How can that be done?

ilimax
Posting Yak Master

164 Posts

Posted - 2004-02-27 : 21:41:10

Select Student_Id,Test_Id,Test_Marks from Table where Test_id =70 or Test_ID=71;

mayhem
Starting Member

11 Posts

Posted - 2004-02-27 : 21:43:59

that will give me only one column, but i want it in two columns.

mayhem
Starting Member

11 Posts

Posted - 2004-02-27 : 22:02:21

i solved my own problem, i used a function to return two tables and inner join them.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-27 : 22:14:17

here is an alternate method:

create table marks ( student_id int, test_id int, test_marks int)
insert into marks
select 0,70,85 union all
select 0,71,80


select student_id,
	max(case when test_id = 70 then test_marks else null end) test1marks,
	max(case when test_id = 71 then test_marks else null end) test1marks
from marks
where student_id = 0
group by student_id

drop table marks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources