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

Question

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.

This type of question has been answered many times. See https://stackoverflow.com/questions/tagged/mysql+pivot-table for a list of past answers. — Bill Karwin, Nov 08 '21 at 15:11
Also, you rarely actually *need* to do a pivot in sql (and especially if you are new to sql, you might overestimate when you need to do it). If you e.g. just want to *display* those students side by side e.g. on a webpage, the usual approach would be to adjust the generating code (e.g. your php script). — Solarflare, Nov 08 '21 at 15:18
@Solarflare I think I should've rephrased my question better (I am going to edit it now). I need to create a view from the first table that looks like the second table given. I am trying to look at examples of how this is done but I do not find any. — noob_programmer, Nov 08 '21 at 15:24
@BillKarwin I have looked around but couldn't find something like this, all I find are tables where the unique keys are new rows but I just need a single row with multiple columns as shown above. It would be very helpful if you already know where an example similar to this is present. Thanks. — noob_programmer, Nov 08 '21 at 15:26
[How can I return pivot table output in MySQL?](https://stackoverflow.com/q/7674786) should cover the available methods to generate a pivot table in mysql. You have to adjust it to your situation, e.g. if you don't want that *"unique keys are new rows"*, don't use a group by. If you have an unknown number of students (and thus: columns), pick/adjust e.g. [this answer](https://stackoverflow.com/a/26297463) in the linked question (won't work as a view though). But to repeat it again: it is possible that it is not actually your task to generate that pivot in sql, I'd tripplecheck this first. — Solarflare, Nov 08 '21 at 15:38

score 3 · Answer 1 · answered Nov 08 '21 at 18:37

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]>

Perfect! But how does it work if we want to use GROUP BY statement. For example if we have another column added , so we have : id, student, marks , subject. For example , James and Jack fall under Subject as 'English' and Jimmy's subject is 'Maths'. How would you rewrite this statement and use GROUP BY subject , which will show the result in two rows for two subjects? Thank you in anticipation — KT2436, Nov 09 '21 at 11:08
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5ff1812ae5561b9b578c46e5cda3ad88 — KT2436, Nov 09 '21 at 14:03
The result should be two rows, one for English and other for 'Maths' — KT2436, Nov 09 '21 at 14:09
yes, it should be basically be something like GROUP BY subject. I have updated a example and made a test table , so the result should be like select*from test https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b7e1060ee25dd3507457794771bbd142 — KT2436, Nov 09 '21 at 15:33

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

1 Answers1