Concatenate strings using SQL's for xml path

Document directory

• 1: Construct initial data
• 2: ask questions
• 4: answer questions
• 4. Answer questions

This article mainly describes how to use SQLFOR XML PATHParameters to concatenate strings,FOR XML PATHIt returns data in the form of an xml file.

Steps:

1: Construct initial data 2: Raise Question 3: A Brief Introduction FOR XML PATH
4: answer questions

 

 

1. Construct initial data

Here is a typical example of a student course. There are three tables: Student, course, and student course.

Table 1: Student

Student_id	Student_name
1	Zhang San
2	Li Si
3	Wang Wu

Table 2: Course

Course_id	Course_name
1	Language
2	Mathematics
3	English

 

Table 3: Student_Course

Student_id	Course_id
1	2
1	3
2	1
2	3
3	3

 

Script:

Create table student
(
Student_id int primary key,
Student_name nvarchar (50) not null
)

Create table course
(
Course_id int primary key,
Course_name nvarchar (50) not null
)

Create table student_course
(
Student_id int not null,
Course_id int not null,
Primary key (student_id, course_id)
)

 

2. Ask questions

Write an SQL statement and the query displays the following results:

Student_name	Course_name
Zhang San	Mathematics, English
Li Si	Language, English
Wang Wu	English

 

3. Brief Introduction FOR XML PATH

FOR XML PATHStatement can generate the queried data XMLData. For example, for student tables, the query results of previous SQL statements are as follows:

 

Select str (student_id) + ',' + student_name from student for xml path ('student ')

Query results:

<Student> 1. Zhang San </student>
<Student> 2, Li Si </student>
<Student> 3, Wang Wu </student>

Student has become a node in an xml file. Let's take a look.FOR XML PATH('') To modify the preceding SQL statement,

 

Select str (student_id) + ',' + student_name from student for xml path ('')

Query results:

 

1. Zhang San 2, Li Si 3, Wang Wu

We can see that this parameter automatically concatenates our query results. In this case, it is easy to concatenate strings!

 

4. Answer questions

To query the expected results, we first use a general SQL statement. After connecting the three tables, the result is:

 

 
 

Select a. student_name, B. course_name from student_course c, student a, course B where
C. student_id = a. student_id and c. course_id = B. course_id

Query results:

Student_name	Course_name
Zhang San	Mathematics
Zhang San	English
Li Si	Chinese
Li Si	English
Wang Wu	English

We can view the query result as a temporary table and connect it to itself.FOR XML PATH('')Parameters to splice the course_name column of the course, and then use the subquery function. In this way, you can obtain all the courses selected by each student. Because multiple records of the same student exist in the table above, you need to group the final results by students. Let's first look at the query statement:

Select student_name,
(Select course_name + ',' from
(
Select student_name, course_name from
(
Select a. student_name, B. course_name from stud_course c, student a, course B where c. student_id = a. student_id and c. course_id = B. course_id
) As
) As B where c. student_name = B. student_name for xml path ('')
) As course_name
From
(
Select a. student_name, B. course_name from student_course c, student a, course B where c. student_id = a. student_id and c. course_id = B. course_id
) As c group by student_name

Query results:

 

Student_name	Course_name
Zhang San	Mathematics, English,
Li Si	Language, English,
Wang Wu	English,

There is also a small problem. course_name is followed by an extra number and the last cropping is performed. Assume that the preceding SQL statement is used as a subquery.

 

Select student_name, left (course_name, len (course_name)-1) from (...) as subquery

In this way, we can get the final result! As you can seeFOR XML PATH('') Parameters are very powerful!

PS: Many people call this row-to-column conversion. I personally don't think so. Although this is a bit like row-to-column conversion, it is more like String concatenation! Let's call it that way!

P.s.: My test environment: SQL server 2008