Can I merge duplicate results into a single result? (SQL)

This is my query:

SELECT 
    CONCAT((SELECT LEFT (Event_Start_Time, LEN(Event_Start_Time) -3)),
            ' ','-',' ', 
           (SELECT LEFT (Event_End_Time, LEN(Event_End_Time) -3))) AS 'Time', 
    CASE WHEN Course_Code IS NULL 
        THEN Course_Title 
        ELSE Course_Code 
    END AS 'Course', 
    CASE WHEN User_Last_Name 
           IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
        THEN NULL 
        ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
    END AS 'Instructor', 
    Room_Name AS 'Room'
FROM 
    Events
    JOIN Courses ON Courses.Course_ID = Events.Course_ID
    JOIN Users   ON Users.User_ID = Events.User_ID
    JOIN Rooms   ON Rooms.Room_ID = Events.Room_ID
WHERE 
    Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
ORDER BY 
    Event_Start_Time, Event_End_Time, Users.User_Last_Name;

Current Output:

08:00 - 11:30     NURS3405 F.Lastname1     LabA
08:00 - 11:30     NURS3405 F.Lastname2     LabB
08:00 - 11:30     NURS3405 F.Lastname3     LabC 
08:00 - 11:30     NURS3405 F.Lastname4     LabD

Expected output:

08:00 - 11:30 NURS3405 F.Lastname1 LabA
                        F.Lastname2 LabB
                        F.Lastname3 LabC
                        F.Lastname4 LabD

And just be one row? I'm not sure if this is possible.

You can do it using the row_number() window function. What (I think that) you want is to PARTITION your data into subgroups, where each partition has a common (Time, Course). Then, for each partition, you want to output Time, Course only on the first row of each partition. In your example, all rows become one single partition; but this could not be the case.

The SQL query you would use would start with your original one, and add a few tricks to "hide" the unwanted output:

; WITH e AS
(
    /* We start with a (slightly simplified) version of the original query */
    SELECT 
        CONCAT(CONVERT(VARCHAR(5),Event_Start_Time, 108), ' - ', CONVERT(VARCHAR(5),Event_End_Time, 108)
        ) AS Time, 
        CASE WHEN Course_Code IS NULL 
            THEN Course_Title 
            ELSE Course_Code 
        END AS Course, 
        CASE WHEN User_Last_Name IN ('Faculty', 'Students', 'Study') OR Course_Title = 'Meeting'
            THEN NULL 
            ELSE CONCAT(substring(User_First_Name, 1, 1), '. ', User_Last_Name)
        END AS Instructor, 
        Room_Name AS Room,
        Event_Start_Time, Event_End_Time, User_Last_Name /* We will still need these columns at output to sort by */ 
    FROM 
        Events
        JOIN Courses ON Courses.Course_ID = Events.Course_ID
        JOIN Users ON Users.User_ID = Events.User_ID
        JOIN Rooms ON Rooms.Room_ID = Events.Room_ID
    WHERE 
        Events.Event_Date = (SELECT CAST(GETDATE()AS Date))
)
/* We use window functions, and partition by (Event_Start_Time, Event_End_Time), order by User_Last_Name. We will output only Time and Course on First Row */
SELECT
    case when (row_number() 
          over (PARTITION BY Event_Start_Time, Event_End_Time 
               ORDER BY User_Last_Name) ) = 1 then Time   end AS Time,
    case when (row_number() 
          over (PARTITION BY Event_Start_Time, Event_End_Time 
                ORDER BY User_Last_Name) ) = 1 then Course end AS Course,
    Instructor, Room
FROM
    e
ORDER BY 
    Event_Start_Time, Event_End_Time, User_Last_Name;

You will get:

Time | Course | Instructor | Room
:------------ | :------- | :----------- | :---
08:00 - 11:30 | NURS3405 | F. Lastname1 | LabA
null | null | F. Lastname2 | LabB
null | null | F. Lastname3 | LabC
null | null | F. Lastname4 | LabD

You can check everything in this DBFiddle.

NOTES:

• You can remove a few SELECT from your original query. They are not needed.

• Your way of "shortening" times seems not to work on all cases. I have used a different approach taken from Stack Overflow's "SQL Server 2005 DateTime (return only hh:mm)".

• I am not sure at all my assumptions about your tables are correct (you are recommended to post the structure of your tables (or the relevant part of it) when posting questions). Advise, otherwise.

• We use the fact that CASE WHEN cond THEN a END will return NULL when cond is not met. If you need an empty string instead of NULL, you must change the expressions to CASE WHEN cond THEN a ELSE '' END.

• Seriously consider the comment from @sp_BlitzErik: this should most probably be done at the presentation layer, not the database layer. It just happens, sometimes, that you can control your database layer better than the presentation one. If that's the case, this is (one possible) alternative. This solution is, in fact, a grouping set in disguise, with a little bit more of flexibility, and without the OLAP cubes background.

References:

• Window Function Examples for SQL Server

• OVER clause

• row_number() function

Data model used for the example:

Tables, PK and FK

CREATE TABLE Rooms
 (
    Room_ID integer PRIMARY KEY,
    Room_Name varchar(50)
 ) ;

 CREATE TABLE Courses
 (
    Course_ID integer PRIMARY KEY,
    Course_Code varchar(50),
    Course_Title varchar(50)
 ) ;

 CREATE TABLE Users
 (
    User_ID integer PRIMARY KEY,
    User_First_Name varchar(50),
    User_Last_Name varchar(50)
 ) ;

 CREATE TABLE Events
 (
    Event_ID integer PRIMARY KEY,
    Course_ID integer REFERENCES Courses(Course_ID),
    Room_ID integer REFERENCES Rooms(Room_ID),
    -- User_ID is most probably the "organizer"/"lecturer", not the "attendant"
    User_ID integer REFERENCES Users(User_ID), 
    Event_Date date,
    Event_Start_Time time,
    Event_End_Time time
 ) ;

Data

```
INSERT INTO Users
(User_ID, User_First_Name, User_Last_name)
VALUES
( 1, 'FirstName1', 'Lastname1'),
( 2, 'FirstName1', 'Lastname2'),
( 3, 'FirstName1', 'Lastname3'),
( 4, 'FirstName1', 'Lastname4') ;

INSERT INTO Courses
(Course_ID, Course_Code, Course_Title)
VALUES
(100, 'NURS3405', 'Title NURS3405') ;

INSERT INTO Rooms
(Room_ID, Room_Name)
VALUES
(201, 'LabA'),
(202, 'LabB'),
(203, 'LabC'),
(204, 'LabD') ;

INSERT INTO Events
(Event_ID, Course_ID, Room_ID, User_ID, Event_Date, Event_Start_Time, Event_End_Ti

StackExchange Database Administrators Q#174091, answer score: 3