hi there
i need to do this for school
i have this structure of tables:
tehnic_detalis(id_detail, detail_name)
products(id_product, id_detail, detail_value)
I wish is to transpose row from tehnic_detalis into column for products in something like this
short_description(id_product, detail_name1, detail_name2...)
help me pls, pls, pls
transpose row into column
Moderator: General Moderators
Assume the following table and data:
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...
Code: Select all
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');
mysql> 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)+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | a | b | d | b |
| Sue | a | c | a | a |
+------+-------+-------+-------+-------+
thx to all...