CODEWITHSUNDEEP
mysqlsqldatabasepivot-tablesql-view
noob_programmer
noob_programmer

Reputation: 11

How to transform all the values in a SQL table into a single row with multiple columns

I need to create a view of this below table

Student Marks
James 500
Jack 200
Jimmy 100

into something like this

Student_1 Marks_1 Student_2 Marks_2 Student_3 Marks_3
James 500 Jack 200 Jimmy 100

I am new to SQL and I am brainstorming on how to do this but I am not able to figure out. Any help would be highly appreciated, thanks.

Upvotes: 0

Views: 289

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Here is a sample for your case. you can use PREPARE statement

SELECT CONCAT( "SELECT ",
     GROUP_CONCAT(
     CONCAT('"',Student,'"'),CONCAT(' AS "Student_',@Nr:=@Nr+1,'", ')
    ,CONCAT('"',Marks,'"'),CONCAT(' AS "Marks_',@Nr,'" ')  SEPARATOR ' , ')
    ," FROM DUAL;") INTO @myquery
FROM stud
CROSS JOIN (SELECT @Nr:=0) as Init
ORDER BY Marks DESC;


## ONLY FOR DEBUG
SELECT @myquery;

PREPARE stmt FROM @myquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sample

MariaDB [Bernd]> SELECT * from stud;
+----+---------+-------+
| id | Student | Marks |
+----+---------+-------+
|  1 | James   |   500 |
|  2 | Jack    |   200 |
|  3 | Jimmy   |   100 |
+----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [Bernd]> SELECT CONCAT( "SELECT ",
    ->  GROUP_CONCAT(
    ->  CONCAT('"',Student,'"'),CONCAT(' AS "Student_',@Nr:=@Nr+1,'", ')
    -> ,CONCAT('"',Marks,'"'),CONCAT(' AS "Marks_',@Nr,'" ')  SEPARATOR ' , ')
    -> ," FROM DUAL;") INTO @myquery
    -> FROM stud
    -> CROSS JOIN (SELECT @Nr:=0) as Init
    -> ORDER BY Marks DESC;
Query OK, 1 row affected (0.00 sec)
    
MariaDB [Bernd]> ## ONLY FOR DEBUG
MariaDB [Bernd]> SELECT @myquery;
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| @myquery                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT "James" AS "Student_1", "500" AS "Marks_1"  , "Jack" AS "Student_2", "200" AS "Marks_2"  , "Jimmy" AS "Student_3", "100" AS "Marks_3"  FROM DUAL; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
    
MariaDB [Bernd]> PREPARE stmt FROM @myquery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [Bernd]> EXECUTE stmt;
+-----------+---------+-----------+---------+-----------+---------+
| Student_1 | Marks_1 | Student_2 | Marks_2 | Student_3 | Marks_3 |
+-----------+---------+-----------+---------+-----------+---------+
| James     | 500     | Jack      | 200     | Jimmy     | 100     |
+-----------+---------+-----------+---------+-----------+---------+
1 row in set (0.00 sec)

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

MariaDB [Bernd]>

Upvotes: 3

Related Questions