Merge data with SQL statements (original)

Merge data with SQL statements (original)

There is a table:
Depid staffname
----------------------
101 aaaaa
101 BBBB
101 cccccc
202 dddddd
202 eee
202 fff
303 ggggg

Table Description: depid is the department ID and staffname is the employee name. Of course, a department has more than 0 .. n employees.

How can I combine the above tables with SQL statements.
Depid staffnames
-------------------
101 AAAAA; BBBB; cccccc
202 dddddd; EEE; fff
303 ggggg

Create the above test data table first

If Exists ( Select Name From Sysobjects Where Name = ' Depstaffs ' And Xtype = ' U ' )
Drop Table Depstaffs

Create Table Depstaffs (
Depid Int Not Null ,
Staffname Varchar ( 30 ) Not Null ,
)
Go

Insert Depstaffs Values ( 101 , ' Aaaaa ' )
Insert Depstaffs Values ( 101 , ' Bbbb ' )
Insert Depstaffs Values ( 101 , ' Cccccc ' )
Insert Depstaffs Values ( 202 , ' Dddddd ' )
Insert Depstaffs Values ( 202 , ' Eee ' )
Insert Depstaffs Values ( 202 , ' Fff ' )
Insert Depstaffs Values ( 303 , ' Ggggg ' )

Create a user-defined function for the sqlserver database: Create   Function   [ DBO ] . [ Fn_getdepstaffnamesbydepid ]
(
@ Depid   Int
)
Returns   [ Nvarchar ] ( 4000 )
As
Begin
Declare   @ Returnvalue   [ Nvarchar ] ( 4000 )
Set   @ Returnvalue   =   ''
Select   @ Returnvalue = @ Returnvalue   +   Ltrim ( Rtrim (Depstaffs. staffname )) +   ' ; '
From Depstaffs
Where Depstaffs. depid =   @ Depid
Set   @ Returnvalue   =   Isnull ( @ Returnvalue , ' ; ' )
Return   @ Returnvalue
End

The usage of custom functions is as follows. Note that [DBO] cannot save:

Select Depid, [ DBO ] . [ Fn_getdepstaffnamesbydepid ] (Depid) As Staffnames
From Depstaffs

:

Duplicate data is displayed, and you can filter out group by. Complete usage:

Select Depid, [ DBO ] . [ Fn_getdepstaffnamesbydepid ] (Depid) As Staffnames
From Depstaffs
Group   By Depid

Final effect: