mssql-Column Record Merge

In project development, there are times when a column record is merged into a row, such as merging the names of people by region, or merging cities according to the initials, the following is an example of merging the names of people by region.

First, build a table, add some data, and build the table code as follows:

If object_id (N ' Demo ') is not Null
Begin
Drop Table Demo
End
Else
Begin
Create Table Demo (
Area nvarchar (30),
Name nvarchar (20))

Insert into Demo (area,name)
Values (n ' Beijing ', N ' Zhang San '),
(n ' Shanghai ', n ' John Doe '),
(n ' Shenzhen ', n ' Harry '),
(n ' Shenzhen ', n ' money Six '),
(n ' Beijing ', n ' Zhao Qi '),
(N ' Beijing ', ' Tom '),
(N ' Shanghai ', ' Amy '),
(N ' Beijing ', ' Joe '),
(N ' Shenzhen ', ' Leo ')
End
Go

After the completion of the query, the table shows the following data:

If we only merge the name column and do not follow any criteria, we can take two methods, the first of which is to use the for XML path method, the code is as follows:

SELECT ', ' +name from dbo. Demo for XML PATH (")

The results of the operation are as follows:

For a more detailed description of the FOR XML Path, refer to MSDN: using the path pattern with FOR XML

The second method is to define a variable to load the results of the query, with the following code:

Declare @NameCollection nvarchar (500)
Select @NameCollection =isnull (@NameCollection + ', ', ') +name from dbo. Demo
Select @NameCollection as Namecollection

The results of the operation are as follows:

The IsNull is added because the first variable @namecollection is null, in order to avoid replacing the "Zhang San" with a comma (",").

It says that you can merge a column unconditionally, but in a project you rarely encounter a situation where you merge data from another column based on one column, for example, we now merge name by area to get the result:

With the above foundation, it is easy to merge such data, we only need to use aggregate group by or duplicate value distinct for the area column, and then merge the name column according to the area column, with the idea of how to implement it, first or the for XML Path method, combined with self-join, first merges the Name column by the area column, with the following code:

SELECT area,
(SELECT ', ' +name from dbo. Demo WHERE area = T.area for XML PATH ("))
As Namecollection from dbo. Demo as T

The results of the operation are as follows:

Now there are two points have not been achieved, the first is the result is repeated, the second is the Namecollection column at the beginning of a comma, first remove the comma, using the stuff function to replace, the code is modified as follows:

SELECT area,
STUFF ((SELECT ', ' +name from dbo. Demo WHERE area = T.area for XML PATH (")), 1, 1, ')
As Namecollection from dbo. Demo as T

The following results are now running:

The following is the left to remove the duplicate data, respectively, using group by and distinct, the code is as follows:

SELECT DISTINCT Area,
STUFF ((SELECT ', ' +name from dbo. Demo WHERE area = T.area for XML PATH (")), 1, 1, ')
As Namecollection from dbo. Demo as T

SELECT area,
STUFF ((SELECT ', ' +name from dbo. Demo WHERE area = T.area for XML PATH (")), 1, 1, ')
As Namecollection from dbo. Demo as T GROUP by area

For the stuff function, refer to the MSDN introduction:Stuff function

The result of the operation is the result we need, at the beginning of the above mentioned a variable to load the query results to merge a column of the method, the following details how to use the above method to achieve our requirements, we can build a function according to the above method, passing an area parameter, according to the area to merge, Returns the merged values, as follows:

CREATE FUNCTION Mergebycolumn
(
--Add The parameters for the function here
@Area nvarchar (30)
)
RETURNS nvarchar (500)
As
BEGIN
--Declare The return variable here
DECLARE @NC nvarchar (500)

--ADD the T-SQL statements to compute the return value here
SELECT @NC =isnull (@NC + ', ', ') +name from dbo. Demo WHERE [email protected]

-Return The result of the function
RETURN @NC

END
GO

After the test is built, take the incoming parameter "Beijing" for example, run the following code:

SELECT dbo. Mergebycolumn (' Beijing ') as Namecollection

The results were as follows:

Now just add the area column to the query, and modify the code as follows:

SELECT area,dbo. Mergebycolumn (area) as namecollection from dbo. Demo

The results are now repeated, as follows:

To repeat the same can be used with group by and distinct, the code below, that can get our final result:

SELECT DISTINCT area,dbo. Mergebycolumn (area) as namecollection from dbo. Demo

SELECT area,dbo. Mergebycolumn (area) as namecollection from dbo. Demo GROUP by area

mssql-Column Record Merge