Select MYSQL rows but rows into columns and column into rows

I want to select all the rows in my database but I want them in inverted sequence. Meaning, I want to use the first column data as the new entities and present entities as the first column. I think you got what I mean

Here is an illustration

id    |     name       | marks
-------------------------------
1     |    Ram         | 45
--------------------------------
2     |    Shyam       |  87

to

id    |   1     |    2     |
----------------------------
开发者_StackOverflow中文版Name  |  Ram    |   Shyam  |
----------------------------
Marks |  45     |    87    | 

With a fixed and known columns, here's how to do it (I took the liberty of naming the table "grades"):

General Idea:

To create a union of different queries and execute it.

Since you need actual data as column headers, the first part of the union will look like:

SELECT 'id', '1', '2', ....

That query alone will duplicate the result, therefore we need to tell MySQL we need to have 0 rows by adding LIMIT 0, 0.

Our first row of the union will contain 'Name', as well as all the data from "Name" column of the table. To get that line we need a query like:

SELECT 'Name',
    (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT Name FROM grades LIMIT 1, 1),
    (SELECT Name FROM grades LIMIT 2, 1),
    ...

Using the same logic, our second row will look like:

SELECT 'Marks',
    (SELECT Marks FROM grades LIMIT 0, 1),
    (SELECT Marks FROM grades LIMIT 1, 1),
    (SELECT Marks FROM grades LIMIT 2, 1),
    ...

Getting the header:

We need to produce a row from MySQL like:

SELECT 'id', '1', '2', ... LIMIT 0, 0;

To get that line we will use CONCAT() and GROUP_CONCAT() functions:

SELECT 'id', 
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades)
LIMIT 0, 0;

and we're going to store that line into a new variable:

SET @header = CONCAT('SELECT \'id\', ',
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades),
    ' LIMIT 0, 0');

Creating the lines:

We need to create two queries like the following:

SELECT 'Name',
    (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT Name FROM grades LIMIT 1, 1),
    (SELECT Name FROM grades LIMIT 2, 1),
    ...

Since we do not know in advance how many rows there are in our original table, we will be using variables to generate the different LIMIT x, 1 statements. They can be produced using the following:

SET @a = -1;
SELECT @a:=@a+1 FROM grades;

Using this snippet, we can create our subqueries:

SELECT GROUP_CONCAT(
    CONCAT(' (SELECT name FROM grades LIMIT ',
        @a:=@a+1,
        ', 1)')
    )
FROM grades

Which we will put into a variable names @line1, along with the first column data (which is the second column's name):

SET @a = -1;
SET @line1 = CONCAT(
    'SELECT \'Name\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Name FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

By following the same logic, the second line will be:

SET @a := -1;
SET @line2 = CONCAT(
    'SELECT \'Marks\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Marks FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

Combining them all:

Our three variables now contain:

@header:
SELECT 'id',  '1', '2' LIMIT 0, 0

@line1:
SELECT 'Name', (SELECT Name FROM grades LIMIT 0, 1),
    (SELECT name FROM grades LIMIT 1, 1)

@line2:
SELECT 'Marks', (SELECT Marks FROM grades LIMIT 0, 1),
    (SELECT marks FROM grades LIMIT 1, 1)

We just need to create a final variable using CONCAT(), prepare it as a new query and execute it:

SET @query = CONCAT('(',
    @header,
    ') UNION (',
    @line1,
    ') UNION (',
    @line2,
    ')'
);

PREPARE my_query FROM @query;
EXECUTE my_query;

Entire solution:

(for testing and reference):

SET @header = CONCAT('SELECT \'id\', ',
    (SELECT GROUP_CONCAT(CONCAT(' \'', id, '\'')) FROM grades),
    ' LIMIT 0, 0');

SET @a = -1;
SET @line1 = CONCAT(
    'SELECT \'Name\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Name FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

SET @a := -1;
SET @line2 = CONCAT(
    'SELECT \'Marks\',',
    (
        SELECT GROUP_CONCAT(
            CONCAT(' (SELECT Marks FROM grades LIMIT ',
                @a:=@a+1,
                ', 1)')
            )
        FROM grades
    ));

SET @query = CONCAT('(',
    @header,
    ') UNION (',
    @line1,
    ') UNION (',
    @line2,
    ')'
);

PREPARE my_query FROM @query;
EXECUTE my_query;

Output:

+-------+------+-------+
| id    | 1    | 2     |
+-------+------+-------+
| Name  | Ram  | Shyam |
| Marks | 45   | 87    |
+-------+------+-------+
2 rows in set (0.00 sec)

Closing thoughts:

• I'm still not sure why you need to transform rows into columns, and I'm sure the solution I presented is not the best one (in terms of performance).

• You can even use my solution as a start and adapt it to a general purpose solution where the table column names (and the number of lines) are not known, using information_schema.COLUMNS as a source, but I guess that's just going too far.

• I strongly believe it is much better to put the original table into an array and then rotate that array, thus getting the data in the desired format.

Sounds like what you're looking for is a crosstab (or transpose) operation on your data.

Here's an excellent article describing how to accomplish this: http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html - it's a bit dated though, not sure if by now there are more efficient ways to go about doing this. It works well enough for me.

On command line you can end your query with \G to achieve this e.g.

select * from `students`\G;