# How to convert a table

**URL:** <https://forums.percona.com/t/how-to-convert-a-table/1187>\
**Category:** Other MySQL® Questions\
**Created:** [June 8, 2009, 1:20am UTC](https://forums.percona.com/t/how-to-convert-a-table/1187 "2009-06-08T01:20:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![redwood\_lin](https://avatars.discourse-cdn.com/v4/letter/r/f475e1/32.png) [@redwood\_lin](https://forums.percona.com/u/redwood_lin)\
**Post date:** [June 8, 2009, 1:20am UTC](https://forums.percona.com/t/how-to-convert-a-table/1187/1 "2009-06-08T01:20:41Z")

</div>

Hi, all,

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

table: list:

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?

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [June 8, 2009, 11:41am UTC](https://forums.percona.com/t/how-to-convert-a-table/1187/2 "2009-06-08T11:41:45Z")

</div>

I’m not sure this is what you want, but it will give you the full set of permutations, even if there are no rows that contain those exact permutations:

SELECT t1.FACILITY, t2.PRODUCT, t3.SEGMENTFROM (SELECT DISTINCT FACILITY AS FACILITY FROM list) AS t1, (SELECT DISTINCT PRODUCT AS PRODUCT FROM list) AS t2, (SELECT DISTINCT SEGMENT AS SEGMENT FROM list) AS t3

---

<div class="post-metadata">

**Author:** ![redwood\_lin](https://avatars.discourse-cdn.com/v4/letter/r/f475e1/32.png) [@redwood\_lin](https://forums.percona.com/u/redwood_lin)\
**Post date:** [June 8, 2009, 8:44pm UTC](https://forums.percona.com/t/how-to-convert-a-table/1187/3 "2009-06-08T20:44:39Z")

</div>

Yes, this is what i want to have a set of combination.

the full SQL code likes this:

SELECT a.\* FROM(SELECT t1.FACILITY, t2.PRODUCT, t3.SEGMENTFROM (SELECT DISTINCT FACILITY AS FACILITY FROM list) AS t1, (SELECT DISTINCT PRODUCT AS PRODUCT FROM list) AS t2, (SELECT DISTINCT SEGMENT AS SEGMENT FROM list) AS t3) aLEFT JOIN list bUSING (FACILITY,PRODUCT,SEGMENT)WHERE b.FACILITY IS NULLORDER BY a.FACILITY,a.PRODUCT,a.SEGMENT