Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   How to convert a table (http://www.go4expert.com/forums/convert-table-t18577/)

gilbertsavier 17Jul2009 12:05

How to convert a table
 
Hi, all,

I have a problem to convert table content based on existing content:

DROP TABLE IF EXISTS `list`;
CREATE TABLE `list` (
`FACILITY` varchar(30) NOT NULL,
`PRODUCT` varchar(30) NOT NULL,
`SEGMENT` varchar(30) NOT NULL,
PRIMARY KEY (`FACILITY`,`PRODUCT`,`SEGMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Let's say I know,
I have 2 FACILITY: AUSTIN, BOSTON;
(by

select distinct facility from list;

)
I have 2 PRODUCT: A, B;
(by

select distinct product from list;

)
I have 2 SEGMENT: BIG, SMALL;
(by

select distinct segment from list;

)

the Total combination could be 2*2*2=8

Now the current table "list" contains below data (3 records):
AUSTIN, A, BIG;
AUSTIN, B, SMALL;
BOSTON, A, SMALL;

How can I tell the other 5 combination by MYSQL?


All times are GMT +5.5. The time now is 08:19.