hlp me pls - SQL Server Forums

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)

hlp me pls

Author

Topic

milleusi
Starting Member

1 Post

Posted - 2006-03-02 : 04:41:39

Assume the following table and data:
Code:
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
mark varchar(15),
score int,
PRIMARY KEY (pkey)
);

insert into exams (name,exam,score) values ('Bob',1,'a',95);
insert into exams (name,exam,score) values ('Bob',2,'b',85);
insert into exams (name,exam,score) values ('Bob',3,'d',63);
insert into exams (name,exam,score) values ('Bob',4,'b',82);

insert into exams (name,exam,score) values ('Sue',1,'a',99);
insert into exams (name,exam,score) values ('Sue',2,'c',70);
insert into exams (name,exam,score) values ('Sue',3,'a',90);
insert into exams (name,exam,score) values ('Sue',4,'a,95');

sql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

i need a table like this table_mark(name, exam1, exam2, exam3, exam4) but with mark instead of score in something like this .....

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | a | b | d | b |
| Sue | a | c | a | a |
+------+-------+-------+-------+-------+

thx to all...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 04:51:15

"pkey int(11) NOT NULL auto_increment,"
Is this on MS SQL Server ?

select 	name,
	max(case when exam = 1 then mark else '' end) as exam1,
	max(case when exam = 2 then mark else '' end) as exam2,
	max(case when exam = 3 then mark else '' end) as exam3,
	max(case when exam = 4 then mark else '' end) as exam4
from exams group by name

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 05:05:44

>>pkey int(11) NOT NULL auto_increment,

I think the questioner is not using SQL Server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources