Select MySQL Rows But Rows into Columns and Column into Rows

Select MYSQL rows but rows into columns and column into rows

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.

How to convert row to column in MySQL

Unfortunately, MySQL does not have PIVOT function, so in order to rotate data from rows into columns you will have to use a CASE expression along with an aggregate function.

Schema and insert statements:

 create table student_det(ID int,   Roll_No int, QNo int);
 insert into student_det values(1      ,21345,         100);
 insert into student_det values(2     , 21345 ,        102);
 insert into student_det values(3    ,  21345  ,       103);
 insert into student_det values(4   ,   21346   ,      100);
 insert into student_det values(5  ,    21346    ,     144);
 insert into student_det values(6 ,     21567     ,    233);
 insert into student_det values(7,      21567      ,   455);

Query:

 with cte as 
 (
   Select Roll_No, QNo,
       row_number() over(partition by Roll_No order by Roll_No) ColumnSequence
   from student_det
 )
 select roll_no,
        max(case when columnsequence=1 then QNo end)Qno1,
        max(case when columnsequence=2 then QNo end)Qno2,
        max(case when columnsequence=3 then QNo end)Qno3
 from cte       
 group by roll_no

Output:

roll_no	Qno1	Qno2	Qno3
21345	100	102	103
21346	100	144	null
21567	233	455	null

How to convert row into columns in SQL?

You want to unpivot the data. The challenge is dealing with the datatypes. You need to convert them all to the same type. Presumably, this only applies to amount and perhaps to accountnumber:

select firstName as anyName from t
union all
select cast(Amount as char) from t
union all
select PostalCode from t
union all
select LastName from t
union all
select cast(AccountNumber as char) from t;

If your table is very large or is really a complicated view, then there are other methods that don't require scanning the table once for each column.

You can also use cross join and case:

select (case when n.n = 1 then firstName
             when n.n = 2 then cast(Amount as char)
             when n.n = 3 then PostalCode
             when n.n = 4 then lastName
             when n.n = 5 then cast(AccountNumber as char)
        end) as anyName
from t cross join
    (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
      ) n

Need to Convert Columns into Rows in MYSQL

May be below solution help you to solve your problem , you need to do some changes as per your table structure.

For this solution you have to make a stored procedure.

If this is your table structure :

CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(500) DEFAULT NULL,
  `value` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

Below solution is working if above is your table structure.

   SET SESSION group_concat_max_len = (2056 * 2056);

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
             CONCAT(
               'MAX(CASE WHEN school.name ="',m.name,'"'
                                ' THEN school.value END)"',m.name , '"'))
                                INTO @sql  
                                            from school as m;

SET @sql = CONCAT('SELECT value,',@sql,
                  ' FROM school');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Do changes as per your table structure.

This solution is also helpful for multiple table ,I hope this may help you to solve your problem.

MYSQL Transpose the rows of the result of a select in column (Only 1 column result)

You can do this with a dynamic statement and PREPARE STATEMENT
like this:

SELECT CONCAT("SELECT "
        , GROUP_CONCAT( column_name ) 
        , " FROM MYTABLE"
        , " WHERE 1=1") into @myQuery
FROM information_schema.columns 
WHERE table_name = 'MYTABLE'
 AND TABLE_SCHEMA = 'textil';

SELECT @myQuery;

PREPARE stmt FROM @MYQUERY;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

sample

MariaDB [textil]> SELECT * from MYTABLE;
+----+------+------+------+
| T0 | T1   | T2   | T3   |
+----+------+------+------+
|  1 |    2 |    4 |    8 |
|  2 |    4 |    8 |   16 |
+----+------+------+------+
2 rows in set (0.00 sec)

MariaDB [textil]> SELECT CONCAT("SELECT "
    ->  , GROUP_CONCAT( column_name ) 
    ->  , " FROM MYTABLE"
    -> , " WHERE 1=1") into @myQuery
    -> FROM information_schema.columns 
    -> WHERE table_name = 'MYTABLE'
    -> AND TABLE_SCHEMA = 'textil';
Query OK, 1 row affected (0.01 sec)

MariaDB [textil]> 
MariaDB [textil]> SELECT @myQuery;
+-------------------------------------------+
| @myQuery                                  |
+-------------------------------------------+
| SELECT T0,T1,T2,T3 FROM MYTABLE WHERE 1=1 |
+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [textil]> 
MariaDB [textil]> PREPARE stmt FROM @MYQUERY;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [textil]> EXECUTE stmt;
+----+------+------+------+
| T0 | T1   | T2   | T3   |
+----+------+------+------+
|  1 |    2 |    4 |    8 |
|  2 |    4 |    8 |   16 |
+----+------+------+------+
2 rows in set (0.01 sec)

MariaDB [textil]> 
MariaDB [textil]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

MariaDB [textil]>

Select MySQL Rows But Rows into Columns and Column into Rows