[SQL] uses SQL to merge the content of specified columns by group to query samwang.

Problem:

When I was working on a project recently, I encountered such a requirement. The simulation requirements are as follows:

Id sname1 Zhang 3 1 Li 4 2 Liu 6 2 Wang 5 result is ID name1 Zhang 3, Li 4 2 Liu 6, Wang 5 queries by ID group, merge the sname content of the same ID with commas (,).

 

Solution:

Pass different IDs to the function to query the combined sname and return it.

-- Create a test table if not exists (select * From sysobjects where id = object_id (N 'studentsinfo') and objectproperty (ID, N 'isusertable') = 1) create Table studentsinfo (ID int, sname nvarchar (20) Go -- add test data insert into studentsinfo values (1, 'zhang san') insert into studentsinfo values (1, 'Li si ') insert into studentsinfo values (2, 'Liu liu') insert into studentsinfo values (2, 'wang 5 ') go -- create function if exists (select * From sysobjects where id = object_id (N 'getstudentsinfonamesbyid') and objectproperty (ID, N 'isscalarfunction') = 1) drop function compute function getstudentsinfonamesbyid (@ id int) returns nvarchar (4000) asbegin declare @ snames nvarchar (4000) set @ snames = ''select @ snames = @ snames + ', '+ sname from studentsinfo where id = @ ID return stuff (@ snames, '') endgo -- Query select ID, DBO. getstudentsinfonamesbyid (ID) as snames from studentsinfo group by ID

 

Summary:

The overall idea is relatively simple, but functions are not used for processing for a lot of time. Here is a record. We will not consider the above efficiency issues for the moment.

 

Link:

The two blog posts below are very good. We can use three methods to meet the above requirements and compare the efficiency. They are very meticulous and recommended! (Thanks to crazyjinn for sharing)

Test and think about SQL function efficiency
Efficiency of SQL one-to-multiple relationship conversion (continued)