transpose row into column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
milleusi
Forum Commoner
Posts: 30
Joined: Mon Jun 13, 2005 3:18 am

transpose row into column

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
milleusi
Forum Commoner
Posts: 30
Joined: Mon Jun 13, 2005 3:18 am

Post 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???
milleusi
Forum Commoner
Posts: 30
Joined: Mon Jun 13, 2005 3:18 am

Post 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()?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
milleusi
Forum Commoner
Posts: 30
Joined: Mon Jun 13, 2005 3:18 am

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