SQL aggregate functions

SQL Server String Aggregation functions

QL Server has several aggregate functions such as SUM, AVG, Count, Count (*), MAX, and MIN, but these functions can only aggregate numeric types and cannot aggregate strings.

As shown in the following table: aggregationtable

Id	Name
1	Zhao
2	Money
1	Sun
1	Li
2	Week

If you want to get the aggregated results

Id	Name
1	Zhaosun Lee
2	Chan Zhou

It is not possible to use SUM, AVG, Count, Count (*), MAX, and Min. Because these are aggregates of values. However, we can solve this problem by customizing the function.
1. First build the test table and insert the test data:

Copy CodeThe code is as follows:
CREATE TABLE aggregationtable (Id int, [Name] varchar (10))
Go
INSERT INTO aggregationtable
Select 1, ' Zhao ' union ALL
Select 2, ' Money ' UNION ALL
Select 1, ' Sun ' UNION ALL
Select 1, ' Li ' union All
Select 2, ' Week '
Go

2. Create a custom string aggregation function

Copy CodeThe code is as follows:
Create FUNCTION aggregatestring
(
@Id int
)
RETURNS varchar (1024)
As
BEGIN
DECLARE @Str varchar (1024)
Set @Str = ' '
Select @Str = @Str + [Name] from aggregationtable
where [Id] = @Id
Return @Str
END
GO

3. Execute the following statement and view the results

Copy CodeThe code is as follows:
SELECT dbo. Aggregatestring (ID), ID from aggregationtable
GROUP BY Id

The result is:

Id	Name
1	Zhaosun Lee
2	Chan Zhou

4> via XML Operation select Stuff ((select ', ' +title from TB for XML Path (') '), 1, 1, ')

SQL aggregate functions