MySQL: Columns to Rows

In this article, we will be looking into how to convert columns to rows. There is often a requirement to get the column-specific data represented as rows and get the necessary information associated with them.

Let us get started by looking into the sample data we will be using in our examples. Assume that we have a table named student_details.

SELECT * FROM student_details;

Output:-

Suppose we want to view the columns student_id, student_name, sex, and age as rows and the data concerning them. Observe the below query for the solution.

SELECT colname,
MAX(CASE WHEN student_name = 'Henry' THEN value ELSE 0 end) Henry,
MAX(CASE WHEN student_name = 'Henric' THEN value ELSE 0 end) Henric,
MAX(CASE WHEN student_name = 'Sandy' THEN value ELSE 0 end) Sandy,
MAX(CASE WHEN student_name = 'Richa' THEN value ELSE 0 end) Richa,
MAX(CASE WHEN student_name = 'Jennifer' THEN value ELSE 0 end) Jennifer
FROM
(
SELECT student_id,student_name ,student_id value ,'student_id' colname  FROM student_details
UNION ALL 
SELECT student_id,student_name ,student_name value ,'student_name' colname  FROM student_details
UNION ALL 
SELECT student_id,student_name ,sex value ,'sex' colname  FROM student_details
UNION ALL 
SELECT student_id,student_name, age value ,'age' colname  FROM student_details
)temp_student_details
GROUP BY colname;

Output:-

We can see, the output in figure 2 shows that the student_id, student_name, sex, and age are presented in rows.

Generic Procedure:

If the requirement is to get the columns represented as rows for a specific student_id, then we can create a procedure for the same and enter the particular student_id value and get the respective rows as and when required. Below is the MySQL code for the generic procedure which will take the student_id as a parameter.

DROP PROCEDURE IF EXISTS CONVERTCOLTOROWS;
DELIMITER ;;
CREATE PROCEDURE CONVERTCOLTOROWS(IN s_id INT)
BEGIN
SET @my_schema='your_schema_name';
SET @my_table='student_details';
SET @clause_where='`student_id`=';
SELECT 
    GROUP_CONCAT(my_query
        SEPARATOR ' UNION ALL ')
INTO @sql FROM
    (SELECT 
        CONCAT('SELECT `student_id`,', QUOTE(COLUMN_NAME), ' AS `key`,`', COLUMN_NAME, '` AS `value` FROM `', @my_table, 
        '` WHERE ', @clause_where, s_id) my_query
    FROM
        (SELECT 
        `COLUMN_NAME`
    FROM
        `INFORMATION_SCHEMA`.`COLUMNS`
    WHERE
        `TABLE_SCHEMA` = @my_schema
            AND `TABLE_NAME` = @my_table) AS `col1`) AS `col2`;
PREPARE stmt1 FROM @sql; 
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
End;
;;
DELIMITER ;

If we want a similar output, let’s say, student_id = 1, we will execute the below query.

CALL CONVERTCOLTOROWS(1); 

Output:-

To get the data corresponding to student_id = 2, execute the below: 

CALL CONVERTCOLTOROWS(2); 

Output:-

We hope the queries in this article helped you to convert columns to rows. Good Luck !!!