How to convert rows into columns

[prasu]

I have a table in which i need to sort the column names.
NOT sort by column names.....sort the COLUMN NAMES.
since it is easy to sort the records.....is there any way by which you can convert column into rows and rows into columns.

[emillion82]

I have a table in which i need to sort the column names.
NOT sort by column names.....sort the COLUMN NAMES.
since it is easy to sort the records.....is there any way by which you can convert column into rows and rows into columns.

Hello Prasu,

I'm not sure if this will help but here's some code I came across recently that gives me similar results in PHP/MySQL

There's a query where I want to get the:
- Username of the user attached to the current opportunity record
- Sales Stage associated with each opportunity record
- Dollar amount associated with opportunity record

Results based upon the original query may look like this:

USER, STAGE, AMOUNT
chris Prospecting 10000
chris Needs Analysis 15000
chris Closed Won 10000
sara Prospecting 5000
sara Closed Won 10000

I'd like for the results to appear as follows:

USER, PROSPECTING, NEEDS ANALYSIS, CLOSED WON
chris 10000
chris 15000
chris 10000
sara 5000
sara 10000

MySQL Query:

$sql = "
SELECT
u.user_name as COUNT_GROUP,
if(o.sales_stage='Prospecting', o.amount, '') as prospecting,
if(o.sales_stage='Qualification', o.amount, '') as qualification,
if(o.sales_stage='Needs Analysis', o.amount, '') as needs_analysis,
if(o.sales_stage='Value Proposition', o.amount, '') as val_prop,
if(o.sales_stage='Id. Decision Makers', o.amount, '') as id_decision,
if(o.sales_stage='Perception Analysis', o.amount, '') as perception_analysis,
if(o.sales_stage='Proposal/Price Quote', o.amount, '') as proposal,
if(o.sales_stage='Negotiation/Review', o.amount, '') as negotiation,
if(o.sales_stage='Closed Won', o.amount, '') as closed_won,
if(o.sales_stage='Closed Lost', o.amount, '') as closed_lost
FROM
opportunities o,
users u
WHERE
o.assigned_user_id = u.id

Here's the PHP to help render your table:

$result = mysql_query($sql) or die(mysql_error());

echo "<table width=600 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
echo "<tr>\n";
foreach ($get_info as $field)
echo "\t<td>$field</td>\n";
echo "</tr>\n";
}
echo "</table>\n";

Using the query, you may be able to do an ORDER BY to sort your results before outputting to the table. I hope this helps.

- E

[smantscheff]

To the best of my knowledge you cannot do this in SQL or MySQL, for that matter.

If you have access to PHP, you can read all column names in an array with mysql_field_name() and then sort and traverse the array.

Also have a look into the INFORMATION_SCHEMA database which contains information about all tables and columns. But don't you ever update it.

[drjohn]

Sorting column names is never required. You specify the order you wish the columns to be displayed in your sql query or, on a php powered web page, in your php.

You are totally wasting your time trying to do this, it serves no useful purpose.