Example: Id txt 1 aaa 1 bbb 2 ccc 3 ddd 3 eee 3 fff Select ID, *** (txt, ';') from TB group by ID Result: 1 AAA; bbb 2 ccc 3 DDD; EEE; fff Method ---------------------------------------------------------------- Create Table Tb (ID int, TXT varchar (100 )) Go Insert into TB Select 1, 'aaa' Union all Select 1, 'bbb 'Union all Select 2, 'ccc 'Union all Select 3, 'ddd 'Union all Select 3, 'Eee 'Union all Select 3, 'fff' Go -- Write an aggregate function: Create Function DBO. fn_merge (@ id int) Returns varchar (8000) As Begin Declare @ r varchar (8000) Set @ r ='' Select @ r = @ r + ';' + TXT from TB where id = @ ID Return Stuff (@ R, 1, 1 ,'') End Go -- Call a function Select ID, DBO. fn_merge (ID) as TXT from TB group by ID Go Drop table TB Drop function fn_merge ------------Stuff Function-------------- Stuff deletes characters of the specified length and inserts a group of characters at the specified start point. The character_expression parameter of the syntax stuff (character_expression, start, length, character_expression) is an expression composed of character data. Character_expression can be a constant, variable, or a column of character or binary data. Start is an integer value that specifies the start position for deletion and insertion. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Length is an integer that specifies the number of characters to delete. If the length is longer than the first character_expression, the last character in the last character_expression can be deleted at most. Return type: If character_expression is a supported character data type, it returns character data. If character_expression is a supported binary data type, binary data is returned. Annotations can be nested with string functions. Example By deleting the three characters starting from the second position (character B) in the first string (abcdef), and then inserting the second string at the start of the deletion, creates and returns a string. Select stuff ('abcdef', 2, 3, 'ijklmn ') The result set is as follows: --------- aijklmnef |