SQL string grouping and aggregation (zt)

This article is reproduced in T-SQL: String grouping and aggregation. Maybe you have a simpler approach?

Today, when reading the subscribed RSS, I saw the following question: how to aggregate the group information in T-SQL and use commas to connect the characters; that is to say, grouping a field in a table and aggregating another field. If the expression is not clear, see the following table.

Original table:

Parent	Child
Charles	William
Charles	Harry
Anne	Peter
Anne	Zara
Andrew	Beatrice
Andrew	Eugenie

Result After processing:

Parent	Children
Charles	William, Harry
Anne	Peter, Zara
Andrew	Eugenie, Beatrice

 

It seems very simple. In my opinion, first write an aggregate function and then call this aggregate function in the query statement. In fact, there is a simpler solution, which is the solution provided by the author, I didn't use a custom aggregate function. He used a processing method like for XML Path ('').

With T (
Select 'Charles 'parent, 'William 'child Union
Select 'Charles ', 'Harry' Union
Select 'Anne ', 'Peter' Union
Select 'Anne ', 'zara' Union
Select 'Andrew ', 'beatrice' Union
Select 'Andrew ', 'ugenie'
)

Select parent, stuff (select ',' + child
From t
Where B. Parent = A. Parent
For XML Path (''), 1, 1,'') Children
From T B
Group by parent

Copy code

 

If you have other solutions, I hope you can give your answers as well.

 

Considering that I am not familiar with the stuff () function, I wrote another method based on this idea:

Select parent, right (list, Len (list)-1) from
(
Select parent,
(Select ',' + children
From t
Where a. Parent = B. Parent
For XML Path ('') as list
From T B
Group by parent
) X

The final result set is the same as the previous stuff function.

In addition, I want to add the usage of stuff functions:

/* Usage description: The stuff (expressionshortstr, startindex, lengthint, numeric) function has four parameters. The function is to delete lengthint characters from the startindex position in expressionshortstr and insert expression2 to the startindex position in expressionshortstr. */ Select 'abcdefg' Select stuff ('abcdefg', 1234, '1234abcdefg') -- The result is '1234abcdefg' Select stuff ('abcdefg', 1234, '1234bcdefg') -- The result is '1234bcdefg' Select stuff ('abcdefg', 1234, '000000') -- The result is 'a1234cdefg' Select stuff ('abcdefg', 1234, '000000') -- The result is 'a1234defg' -- The general programming language, like the SQL language, treats strings as character arrays, but the difference is that most programming languages use the array subscript to start at 0, the SQL Server is 1. Due to inertial thinking, the 0 starting position in the general programming language is often brought to SQL programming.

Original table:

Parent	Child
Charles	William
Charles	Harry
Anne	Peter
Anne	Zara
Andrew	Beatrice
Andrew	Eugenie

Result After processing:

Parent	Children
Charles	William, Harry
Anne	Peter, Zara
Andrew	Eugenie, Beatrice

 

It seems very simple. In my opinion, first write an aggregate function and then call this aggregate function in the query statement. In fact, there is a simpler solution, which is the solution provided by the author, I didn't use a custom aggregate function. He used a processing method like for XML Path ('').

With T (
Select 'Charles 'parent, 'William 'child Union
Select 'Charles ', 'Harry' Union
Select 'Anne ', 'Peter' Union
Select 'Anne ', 'zara' Union
Select 'Andrew ', 'beatrice' Union
Select 'Andrew ', 'ugenie'
)

Select parent, stuff (select ',' + child
From t
Where B. Parent = A. Parent
For XML Path (''), 1, 1,'') Children
From T B
Group by parent

Copy code

 

If you have other solutions, I hope you can give your answers as well.