SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tickets
             (ticket_id integer,
              issue varchar(max));
INSERT INTO tickets
            (ticket_id,
             issue)
            VALUES (100,
                    'Power Outage'),
                   (101,
                    'Internet is not working');
2 rows affected

CREATE TABLE departments
             (department_id integer,
              department varchar(256));
INSERT INTO departments
            (department_id,
             department)
            VALUES (1,
                    'ALL'),
                   (2,
                    'Accounting'),
                   (3,
                    'Human Resources'),
                   (4,
                    'Receiving');
4 rows affected

CREATE TABLE departmenttickets
             (ticket_id integer,
              department_id integer);
INSERT INTO departmenttickets
            (ticket_id,
             department_id)
            VALUES (100,
                    2),
                   (100,
                    3),
                   (101,
                    1);
3 rows affected

SELECT t.issue, 
       stuff((SELECT ', ' + cast(d.department AS varchar(max))
                     FROM departments d
                          INNER JOIN departmenttickets dt
                                     ON dt.department_id = d.department_id
                                         OR dt.department_id = 1
                     WHERE dt.ticket_id = t.ticket_id
                     FOR XML PATH('')),
             1,
             2,
             '') departments
       FROM tickets t
       WHERE t.ticket_id = 101
       ORDER BY t.issue;


issue
departments


Internet is not working
ALL, Accounting, Human Resources, Receiving