SQL: How do I merge multiple rows into one when they meet a certain criteria and create a new row when it meets a certain criteria?

Question

I'm writing a SQL code and want to merge multiple rows into one when a variable in the rows meet a certain criteria. I also want to create a new row when a criteria is met.

My table is called CLASSES

Basically here is what I want to do:

IF CLASS# = CLASS_ROOM THEN MERGE THOSE ROWS 
START A NEW ROW WHEN CLASS_TIME >='12:30'

I WANT TO GO FROM THIS:

CLASS_NAME     CLASS #     CLASS_TIME     CLASS_ROOM
    PHY         1280           7:00           1280
    SCI         1280           9:00           1280
    COM         1280           12:00          1280
    DRO         1738           01:00          1738
    PIL         1738           03:00          1738

TO THIS:

PHY 1280 7:00 1280 SCI 1280 9:00 1280 COM 1280 12:00 1280 

DRO 1738 1:00 1738 PIL 1738 3:00 1738

MySQL *or* SQL Server? MSSQL != MYSQL and there is no universal way to denormalize the requested output. It would probably be better handle this denormalization in the client application. The schema also looks .. suspect (eg. how can different classes have the same class number?), but that's a different question/issue. — user2864740, Jul 10 '15 at 22:25
SQL Server and I'm just using this as an example. My actual table and data makes more sense. — Ben, Jul 10 '15 at 22:34
I do not believe that your sample includes a NEW ROW case? What if you add something like: `NEW 1280 12:30 1280`? — PM 77-1, Jul 10 '15 at 22:34

score 0 · Answer 1 · edited May 23 '17 at 11:58

This one isn't hard in MySQL, because that table server boasts GROUP_CONCAT(). (http://sqlfiddle.com/#!9/b507fe/12/0)

    SELECT GROUP_CONCAT(
          CONCAT(CLASS_NAME, ' ', 
                 CLASS, ' ',
                 DATE_FORMAT(CLASS_TIME,'%H:%i'), ' ',
                 CLASS_ROOM)
          ORDER BY CLASS_TIME
          SEPARATOR ' '
       ) class_list
   FROM CLASS
  GROUP BY HOUR(CLASS_TIME) DIV 12, CLASS_ROOM
  ORDER BY CLASS_ROOM, HOUR(CLASS_TIME) DIV 12

In Microsoft SQL Server it takes all sorts of arcane monkey business, about which you can read. Simulating group_concat MySQL function in Microsoft SQL Server 2005?

SQL: How do I merge multiple rows into one when they meet a certain criteria and create a new row when it meets a certain criteria?

1 Answers1