Turn rows into columns.

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
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Turn rows into columns.

Post by JellyFish »

Is there a way in mysql to turn rows into columns, or reverse/flip a table. I know this doesn't make much sense when I say this, but maybe you'll understand if you knew more about what I'm trying to do. Let's say I had a table with two rows: name and number. What I'm trying to do is turn the result set of a query to this table into a table with the same number of columns as there are rows and each column's name is the name field from the original table. The result set would contain only one row of integers from the number field of the original table. Let me illustrate a bit more:

Original table:
| name | number |
| Joe | 5551234 |
| John | 5554321 |
| Jim | 5555555 |

Let's say I had a table like the above with the three rows illustrated. I'm looking to return a result set based on this table like:

Result table:
| Joe | John | Jim |
| 5551234 | 5554321 | 5555555 |

What happens is each row in the original table get's a specific column in the result table with the name of a specific column from the original table and all the rest of the columns from the original table a row in the result table.

Is there a way to do the exact same thing in the example above?

Any help on this is very so much appreciated. Thanks for reading.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Turn rows into columns.

Post by John Cartwright »

Code: Select all

$return = array();
while ($row = mysql_fetch_assoc($result)) {
   $return[$row['name']] = $row['number'];
}
:?:
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Turn rows into columns.

Post by JellyFish »

John Cartwright wrote:

Code: Select all

$return = array();
while ($row = mysql_fetch_assoc($result)) {
   $return[$row['name']] = $row['number'];
}
:?:
I'm wondering if I could do this in MySQL, or is it better to do this in PHP?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Turn rows into columns.

Post by John Cartwright »

Mysql does not support dynamic column names. You need to iterate the result set at some point anyways, and I would even consider good practice to store the result set type object or array (better seperation).
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Turn rows into columns.

Post by JellyFish »

Okay, I'll just go with the PHP solution. :D
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Turn rows into columns.

Post by VladSun »

I think (AFAIR) your last two posts are related to "formatting data using DB (SQL)" ... - well, you should not use SQL for such purposes - it should be done in code layer.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply