I have a system where students from different departments can create a group. I have the following tables:
Table Groups
:
GroupID | GroupName | GroupStatus
--------+------------+-------------
1 | Group - 01 | Incomplete
2 | Group - 02 | Complete
3 | Group - 03 | Complete
Table GroupMembers
GroupID | StudentID
--------+-----------
1 | 203313
1 | 208122
1 | 207432
2 | 212343
2 | 210434
2 | 209243
2 | 211003
Table Student
:
Student ID | Department ID ..
-----------+----------------
203313 | 2
208122 | 3
207432 | 2
212343 | 3
210434 | 4
209243 | 1
211003 | 3
Table Department
:
DepartmentID | ShortCode | Name
-------------+-----------+------------------------
1 | EE | Electrical Engineering
2 | CS | Computer Science
3 | ME | Mechanical Engineering
4 | SE | System Engineering
I want to create a view in which I can get Group Name, and Group Members Department and Count.
So output should be something like this:
GroupID | GroupName | MembersDistribution
--------+------------+------------------------
1 | Group - 01 | CS - 2, ME - 1
2 | Group - 02 | EE - 1, ME - 2, SE - 1
Is a query for such a view possible?
2条答案
按热度按时间vh0rcniy1#
Here's one way using
STRING_AGG()
(SQL Server 2017+) against the departments first, then joining to groups:Example in this db<>fiddle .
8nuwlpux2#
Yes, you can create a view that provides the desired output with the given tables. You can achieve this by using the
JOIN
andGROUP BY
clauses in SQL. Here's the query to create the view:The
GroupMembersDistribution
view will have three columns:GroupID
,GroupName
, andMembersDistribution
. TheCONCAT
function is used to concatenate the department's shortcode and the count of students in each group from the same department. TheJOIN
statements link the tables together based on the corresponding IDs.Now, when you query the view, you should get the desired output:
Output:
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.