Add multiple rows in just one row from a single table

Question

How can I join multiple rows in just one single row through mysql?

Example :

Student Table

Sno.| Name  |  Subjects
1.  | ABC   |  English
2.  | ABC   |  Mathematics
3.  | ABC   |  Science
4.  | FMC |  French
5.  | ABC   |  Russian
6.  | JBC   |  French

Now I want it in this format

Sno.| Name |   Sub1 |  Sub2 | Sub3 |  Sub4 |
1.  | ABC |   Eng  |  Maths| Science| Russian
2.  | FMC |    French| Null| Null   | Null
3.  | JBC |   French| Null | Null   | Null

I am not sure how to actually do it? And shall I create a view or a table?

I guess a view will be fine.

Do you really need separate columns? Otherwise you could use `GROUP_CONCAT`. And what do you want to do with that data afterwards? — knittl, Jul 16 '12 at 17:33
check this out: http://stackoverflow.com/questions/7674786/mysql-pivot-table — codingbiz, Jul 16 '12 at 17:39
@IPerfect : For printing in pdf, which language you are using? — Fahim Parkar, Jul 16 '12 at 17:56
@IPerfect : Please check my answer and let me know if you have any queries... — Fahim Parkar, Jul 16 '12 at 18:03

score 3 · Accepted Answer · answered Jul 16 '12 at 19:59

I agree with the other answers, that GROUP_CONCAT along with PHP to split the comma separated values is probably the best approach, however if for any other reason you needed the output you suggested via Pure SQL I would suggest one of the following appoaches.

1. Self Joins

SELECT  t1.Name, 
        MIN(t1.Subject) AS Sub1,
        MIN(t2.Subject) AS Sub2,
        MIN(t3.Subject) AS Sub3,
        MIN(t4.Subject) AS Sub4
FROM    Students t1
        LEFT JOIN Students T2 
            ON t1.Name = t2.Name 
            AND t2.Subject > t1.Subject
        LEFT JOIN Students T3 
            ON t2.Name = t3.Name 
            AND t3.Subject > t2.Subject
        LEFT JOIN Students T4 
            ON t3.Name = t4.Name 
            AND t4.Subject > t3.Subject
GROUP BY t1.Name;

2. Using a ROW_NUMBER Type function to aggregate

SELECT   Name,
         MAX(IF(RowNum = 1,Subject, NULL)) AS Sub1,
         MAX(IF(RowNum = 2,Subject, NULL)) AS Sub2,
         MAX(IF(RowNum = 3,Subject, NULL)) AS Sub3,
         MAX(IF(RowNum = 4,Subject, NULL)) AS Sub4
FROM     (    SELECT   Name,
                       Subject,
                       @r:= IF(@Name = Name, @r + 1, 1) AS RowNum,
                       @Name:= Name AS Name2
              FROM    Students,
                      (SELECT @Name:='') n,
                      (SELECT @r:= 0) r
              ORDER BY Name, Sno
          ) t
GROUP BY Name

score 1 · Answer 2 · answered Jul 16 '12 at 18:02

1

Using below query, get the Name and his/ her subjects.

SELECT Name, GROUP_CONCAT(Subjects) AS List
FROM myTable
GROUP BY Name

Then in PHP, you can use implode function for printing subjects.

Hope this helps.

Demo at sqlfiddle

answered Jul 16 '12 at 18:02

Fahim Parkar

30,974
45
160
276

Jocelyn · Answer 3 · 2012-07-16T17:41:59.377

0

Try using GROUP BY and GROUP_CONCAT:

SELECT Name, GROUP_CONCAT(Subjects) AS Subjects_list
FROM students_table
GROUP BY Name

Then use PHP function explode while fetching records to get the different values stored in Subjects_list column.

edited Jul 16 '12 at 17:41

answered Jul 16 '12 at 17:35

Jocelyn

11,209
10
43
60

Hmmm, let me check. Thanks for the help. – I Perfect Jul 16 '12 at 17:46

score 0 · Answer 4 · answered Jul 16 '12 at 18:15

It ain't pretty!

To use the above solutions, you could do:

SELECT 
  Sno, 
  Name,
  NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(all_subjects, ',', 1), ',', -1), '') AS Sub1,
  NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(all_subjects, ',', 2), ',', -1), '') AS Sub2,
  NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(all_subjects, ',', 3), ',', -1), '') AS Sub3,
  NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(all_subjects, ',', 4), ',', -1), '') AS Sub4
FROM (
  SELECT Sno, Name, CONCAT(GROUP_CONCAT(Subjects ORDER BY Sno),',,,') AS all_subjects FROM table GROUP BY name
) inner_sel
;

Your requests leads to the assumption there can be never more than 4 subjects. You also require NULLs where there is no mention of four subjects.

There kind of requests lead to a forced SQL code, one which is typically what SQL is intended for.

To explain the above: we use GROUP_CONCAT, then break it again to pieces. Since there could be fewer than 4 elements, we pad with commas (',,,'). We then break each piece according to its place within the concatenated string, and return with NULL if it's empty.

Can't say this is one of my better answers in terms of prettiness. I hope you find it useful.

You'll have to create two views, because of the derived table. So, a view for the inner SELECT, another that reads from it. — Shlomi Noach, Jul 16 '12 at 18:22

Add multiple rows in just one row from a single table

4 Answers4

Demo at sqlfiddle

Linked