All rows of a certain field in the database are concatenated

Recently, I encountered a problem where I had to query some information from the Internet by spelling a field in the data table. I found that only the scalar function was written.

.

-- 1 create a table
Create Table product (productid varchar (20), productname varchar (20 ))
Go

-- 2 Data
Insert into product values ('w001', 'hongda ')
Insert into product values ('w002', 'apsara ')
Insert into product values ('w003 ', 'Hero ')

Insert into product values ('w004 ', 'Hero ')
Insert into product values ('w005, 'Hero ')
Insert into product values ('w006 ', 'human ')
Go

-- 3 create a user-defined function
Create Function getstr (@ pname varchar (20 ))
Returns varchar (8000)
As
Begin
Declare @ retstr varchar (8000)
Set @ retstr =''
Select @ retstr = @ retstr + ',' + productid from product where productname = @ pname
Set @ retstr = stuff (@ ret, 1, 1 ,'')
Return @ retstr
End
Go

-- 4 run
Select productid = DBO. getstr (productname), productname
From Product
Group by productname order by productname

The execution result, um, finally reaches the desired output.