Select MYSQL rows, but enter rows and columns in rows

Question

I want to select all rows in my database, but I want them to be in inverted order. Meaning, I want to use the first column data as new objects and existing objects as the first column. I think you understand what I mean

Here is an illustration

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

to

id    |   1     |    2     |
----------------------------
Name  |  Ram    |   Shyam  |
----------------------------
Marks |  45     |    87    | 

Answer 1

With fixed and well-known columns, here's how to do it (I let you call the table "ratings"):

General idea:

To create a union of different queries and execute it.

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

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

, MySQL, 0 , LIMIT 0, 0.

'Name', "" . , :

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

, :

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

:

MySQL, :

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

, CONCAT() GROUP_CONCAT ( ):

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

:

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

:

, :

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

, , LIMIT x, 1. , :

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

, :

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

@line1 ( ):

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
    ));

:

:

@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)

CONCAT(), :

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

PREPARE my_query FROM @query;
EXECUTE my_query;

:

( ):

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;

:

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

:

• , , , ( ).

• , ( ) , information_schema. COLUMNS , , .

• , , .

Answer 2

, , , - - ( ) .

, , : http://onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html - , , , . .

Answer 3

\G, . .

select * from `students`\G;