Page 1 of 1
transpose row into column
Posted: Tue Feb 28, 2006 4:42 am
by milleusi
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
Posted: Tue Feb 28, 2006 9:25 am
by feyd
Doing so is not very flexible, are you sure you want to denormalize data from tehnic_details?
What do you have written so far? If nothing, it would help to know more specifics on what you want to do and how you want to do it.
Posted: Tue Feb 28, 2006 12:38 pm
by milleusi
this is a one solution but i need to work for TRUE case to obtain proper value
SELECT id_detail, sum(
IF (
id_detail =4, detail_value, FALSE
) ) AS detail_name1
FROM products
WHERE id_product =31
GROUP BY id_product
eny idee to get detail_value because is varchar???
Posted: Tue Feb 28, 2006 11:36 pm
by milleusi
SELECT id_detail, sum(
IF (
id_detail =4, cast(detail_value as char), FALSE
) ) AS detail_name1
FROM products
WHERE id_product =31
GROUP BY id_product
this works fine when detail_value is int......
how to make to work when detail_value is varchar
what to use instead of sum()?
Posted: Wed Mar 01, 2006 12:33 am
by feyd
You're not making a lot of sense at the moment. If you'd explain in more detail, it may help others help you.
I'd suggest, at a minimum, answering my previous questions.
Posted: Wed Mar 01, 2006 1:31 am
by milleusi
Assume the following table and data:
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)
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...