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...