As shown in the following table: aggregationtable
| Id |
Name |
| 1 |
Zhao |
| 2 |
Money |
| 1 |
Sun |
| 1 |
Li |
| 2 |
Week |
If you want the aggregate results of the following diagram
| Id |
Name |
| 1 |
Zhaosun Lee |
| 2 |
Chan Zhou |
You can't do it with Sum, AVG, Count, Count (*), MAX, and Min. Because these are aggregates of values. But we can solve this problem by customizing the function.
1. First set up the test table and insert the test data:
Copy Code code 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, ' Zhou '
Go
2. Create a custom string aggregate function
Copy Code code 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 Code code as follows:
SELECT dbo. Aggregatestring (ID), ID from aggregationtable
GROUP BY Id
The results are:
| Id |
Name |
| 1 |
Zhaosun Lee |
| 2 |
Chan Zhou |
