SQL - SELECT Merge rows if multiple results are found

Question

I have an SQL table setup similar to this:

name   |  subject |
-------+----------+
Harry  | Painting |
Sandra | Soccer   | 
Sandra | English  |

How can I write a select statement that merges the rows if they have multiple subject, so it would output a result like this:

name   |  subject 1 |  subject 2 |
-------+------------+------------+
Harry  | Painting   |            |
Sandra | Soccer     |   English  |

Is the number of subjects fix per person (`name`)? Otherwise, you need to read more about dynamic. — John Woo, Apr 11 '14 at 01:52
If it's OK to get the merged subjects into a single column, use `GROUP_CONCAT`. Otherwise, see http://stackoverflow.com/questions/7674786/mysql-pivot-table — Barmar, Apr 11 '14 at 01:53
I need all the results to be done in 1 select statement as this how the reporting system works. But i cannot create new tables. — user2562674, Apr 11 '14 at 01:55

score 0 · Answer 1 · answered Apr 11 '14 at 01:55

You shouldn't. The best approach is to join the tables so you have:

Harry, Painting
Sandra, Soccer
Sandra, English

And then process these rows in your scripting language (PHP etc) to turn it into the hierarchical data structure you desire

In your example above, what would happen when there's 3 subjects per person, 10, 100, etc.

SQL only really works with two dimensional data. For three dimensions you either need to pre/post process as i've suggested, or move to something like NoSQL mongoDB that deals with structured objects instead of table rows.

score 0 · Answer 2 · answered Apr 11 '14 at 02:01

Since you mentioned that the maximum number of subjects is only 2, you can therefore, generate a sequential number for each name and used that to pivot the columns.

SELECT  Name,
        MAX(CASE WHEN rn = 1 THEN Subject END) Subject1,
        MAX(CASE WHEN rn = 2 THEN Subject END) Subject2
FROM
(
    SELECT  A.name, 
            A.subject, 
            (
                SELECT  COUNT(*)
                FROM    tableName c
                WHERE   c.name = a.name AND
                        c.subject <= a.subject) AS rn
    FROM    TableName a
) aa
GROUP BY Name

SQLFiddle Demo

The above is an SQL way.

score 0 · Answer 3 · answered Apr 11 '14 at 02:26

0

You need a PIVOT routine. Serach for this in the engine of your choice.

Some RDBMSs have this built in. There is an alternative using a CASE statement in the SELECT clause, for which there are many blog posts out there.

answered Apr 11 '14 at 02:26

Michael Green

1,397
1
17
25

score 0 · Answer 4 · answered Apr 11 '14 at 03:13

You can accomplish this in one statement using a combination of substring_index() and group_concat() like this (SQLfiddle)

SELECT DISTINCT s.name, 
       substring_index(p.subject_list, ',', 1) AS "subject_1",
       IF(instr(p.subject_list, ','), 
          substring_index(p.subject_list, ',', -1), 
          NULL
       ) AS "subject_2"
  FROM subjects s
  JOIN (SELECT name, GROUP_CONCAT(subject) AS "subject_list"
          FROM subjects
         GROUP BY name
       ) p on p.name = s.name
;

SQL - SELECT Merge rows if multiple results are found

4 Answers4