Merging Sql Rows based on same datetime

Question

I'm struggeling at the following task. I have a table like this:

+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+
| Id  |      Assigned Date      |   Assigned Starttime    |    Assigned Endtime     | lesson_subject_id | lesson_teacher_id | lesson_classes_id |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+
| 116 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK             | Adlberger         | CL_HEB 15/I B     |
| 112 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK             | Jakob             | CL_HEB 15/I A     |
| 113 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK             | Jakob             | CL_HEB 15/I A     |
| 117 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK             | Adlberger         | CL_HEB 15/I B     |
| 118 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK             | Adlberger         | CL_HEB 15/I B     |
| 114 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK             | Jakob             | CL_HEB 15/I A     |
| 115 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK             | Jakob             | CL_HEB 15/I A     |
| 119 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK             | Adlberger         | CL_HEB 15/I B     |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+-------------------+

As you can see there are entries which starts and ends at the same time.

What I need to do is to merge the cells togheter and delete the needless rows.

The result shoult look like this

+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+
| Id  |      Assigned Date      |   Assigned Starttime    |    Assigned Endtime     | lesson_subject_id | lesson_teacher_id |         lesson_classes_id        |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+
| 116 | 2016-03-03 00:00:00.000 | 1900-01-01 13:15:00.000 | 1900-01-01 14:00:00.000 | SU_SK             | Adlberger, Jakob  | CL_HEB 15/I A, CL_HEB 15/I B     |
| 113 | 2016-03-03 00:00:00.000 | 1900-01-01 14:15:00.000 | 1900-01-01 15:00:00.000 | SU_SK             | Adlberger, Jakob  | CL_HEB 15/I A, CL_HEB 15/I B     |
| 118 | 2016-03-03 00:00:00.000 | 1900-01-01 15:15:00.000 | 1900-01-01 16:00:00.000 | SU_SK             | Adlberger, Jakob  | CL_HEB 15/I A, CL_HEB 15/I B     |
| 115 | 2016-03-03 00:00:00.000 | 1900-01-01 16:15:00.000 | 1900-01-01 17:00:00.000 | SU_SK             | Adlberger, Jakob  | CL_HEB 15/I A, CL_HEB 15/I B     |
+-----+-------------------------+-------------------------+-------------------------+-------------------+-------------------+----------------------------------+

Any ideas how I can achieve this? I'm on Sql Server 2008 R2

Regards Lukas

Please tag your question with the database you are using. – Gordon Linoff Jul 22 '15 at 12:11 — Gordon Linoff, Jul 22 '15 at 12:11

Milan · Answer 1 · 2015-07-22T12:13:17.940

Without going into the solution, if you are trying to update your table like you suggested: this seems like bad database (re)design, imo.

Why would you want to do this is the first question?

You can group the data on UI (some development is required) or change the design by splitting lesson_subjects and lesson_classes/teachers tables and establishing relationship between them. Sorry this is probably not the answer you needed :)

Edit: I see that other people do have similar questions, so I was intrigued to find the answer. One way to do it in oracle (building on top of the answer of Kibadachi):

SELECT  MIN(ID),
        ASSIGNED_DATE , 
        ASSIGNED_STARTTIME,
        ASSIGNED_STARTTIME, 
        LESSON_SUBJECT_ID,
        LISTAGG(TRIM(LESSON_TEACH_ID) , ',') WITHIN GROUP (ORDER BY LESSON_TEACH_ID ) AS LESSON_TEACH_ID,
        LISTAGG(TRIM(LESSON_CLASS_ID) , ',') WITHIN GROUP (ORDER BY LESSON_CLASS_ID ) AS LESSON_CLASS_ID 
FROM ATABLE
GROUP BY ASSIGNED_DATE, 
        ASSIGNED_STARTTIME,
        ASSIGNED_STARTTIME, 
        LESSON_SUBJECT_ID;

Unfortunately i'm on SQL 2008 R2 is there a similar solution? — Lukas, Jul 22 '15 at 17:43
i know that this is a bad design... but I need to mention that this table is generated by a dataimport which reads data from an XML. The import service is only capable of importing data in a flat format. Therefore I need to cleanUp all the data, btw. this is done in memory in a temporary table. Once the data is cleanedUp i start importing the records into the correct tables which are then accessed by the application. — Lukas, Jul 23 '15 at 12:03

score 0 · Answer 2 · answered Jul 22 '15 at 11:31

You want the lesson_teacher_id column concatenated so : Plan A:

SELECT  assignedDate , 
        assignedStarttime , 
        assignedEndtime , 
        lesson_subject_id , 
        lesson_classes_id , 
        LISTAGG(lesson_teacher_id , ',')WITHIN GROUP (ORDER BY lesson_teacher_id ) AS lesson_teacher_id 
FROM myTable 
GROUP BY assignedDate,
         assignedStarttime ,
         assignedEndtime ;

PLAN B :

SELECT  assignedDate , 
            assignedStarttime , 
            assignedEndtime , 
            lesson_subject_id , 
            lesson_classes_id , 
            wm_concat(lesson_teacher_id , ',')WITHIN GROUP (ORDER BY lesson_teacher_id ) AS lesson_teacher_id 
    FROM myTable 
    GROUP BY assignedDate,
             assignedStarttime ,
             assignedEndtime ;

Merging Sql Rows based on same datetime

2 Answers2