Here's one way using STRING_AGG() (SQL Server 2017+) against the departments first, then joining to groups:

-- CREATE VIEW dbo.ViewName 
-- AS
SELECT ga.GroupID, g.GroupName, ga.MembersDistribution
  FROM
  (
   SELECT GroupID, 
    MembersDistribution = STRING_AGG(CONCAT(ShortCode, ' - ', c), ', ')
   FROM
   (
     SELECT g.GroupID, d.ShortCode, c = COUNT(*) 
     FROM dbo.GroupMembers AS g
     INNER JOIN dbo.Student AS s
     ON g.StudentID = s.StudentID
     INNER JOIN dbo.Department AS d
     ON s.DepartmentID = d.DepartmentID
     GROUP BY g.GroupID, d.ShortCode
    ) AS x 
    GROUP BY GroupID
  ) AS ga
  INNER JOIN dbo.Groups AS g
  ON ga.GroupID = g.GroupID;

Example in this db<>fiddle .

Yes, you can create a view that provides the desired output with the given tables. You can achieve this by using the JOIN and GROUP BY clauses in SQL. Here's the query to create the view:

CREATE VIEW GroupMembersDistribution AS
SELECT 
    g.GroupID,
    g.GroupName,
    CONCAT(d.ShortCode, ' - ', COUNT(m.StudentID)) AS MembersDistribution
FROM 
    Groups g
JOIN 
    GroupMembers m ON g.GroupID = m.GroupID
JOIN 
    Student s ON m.StudentID = s.StudentID
JOIN 
    Department d ON s.DepartmentID = d.DepartmentID
GROUP BY 
    g.GroupID, g.GroupName, d.ShortCode;

The GroupMembersDistribution view will have three columns: GroupID , GroupName , and MembersDistribution . The CONCAT function is used to concatenate the department's shortcode and the count of students in each group from the same department. The JOIN statements link the tables together based on the corresponding IDs.

Now, when you query the view, you should get the desired output:

SELECT * FROM GroupMembersDistribution;

Output:

GroupID | GroupName  | MembersDistribution
-------------------------------------------
1       | Group - 01 | CS - 2, ME - 1
2       | Group - 02 | EE - 1, ME - 2, SE - 1

This will give you a view with the Group ID, Group Name, and the distribution of members' departments along with their counts for each group.