SQL server String Summary

I am developing a small project-"Graduation Design Management for students", which includes the student selection module, as shown in 1, the required function is to dynamically display information about a question when you place the cursor over the question, for example, the "topic Introduction", "selection requirements", "number of subjects allowed", and "number of selected students" information are better handled. The key is the "List of students who have selected the question ".

Figure 1

Idea 1: Design a list of selected students (nvarchar (max) to dynamically modify the content of this field. It is easier to add the students who want to answer the questions. Just concatenate the strings and delete them, you can use string. replace (string oldValue, string newValue) parses the string. However, this design does not meet 1NF requirements and is inconvenient.

Train of Thought 2: design table structure 2 shows that the design fully complies with 1NF, 2NF, and 3NF. The question is how to select student_no according to the question (title_id )? Effect 3:

Figure 2

Figure 3

My practice is as follows:

First, write a summary function:

1 ALTER function [dbo]. [getstr] (@ id int)
2
3 returns varchar (2000)
4
5
6
7 begin
8
9 declare @ str nvarchar (2000)
10
11 set @ str =''
12
13 select @ str = @ str + ',' + rtrim ([student_name]) from v_teacher_student where title_id = @ Id order by title_id
14
15 set @ str = right (@ str, len (@ str)-1)
16
17 return @ str
18
19 end
20
21

Then create a view:

 

1 alter view [dbo]. [v_teacher_student_name]
2
3AS
4
5 select distinct title_id, dbo. getstr (title_id) AS aaa
6
7 FROM dbo. v_teacher_student
8
9 WHERE (title is not null)
10

 

You can get 3 results.

The above is my practice, which may not be the best. I hope to inspire others or inspire others.