SQL server multi-row data splicing instance method

1. Table Structure
Id type productCode
1 pencil 0001
2 pencil 0002
3 pencil 0003
4 pen 0004
5 pen 0005
6 pen 0004
7 ballpoint pen 0007
8 ballpoint pen 0008
9 ballpoint pen 0007
2. Custom function fun
Copy codeThe Code is as follows:
GO
/***** Object: UserDefinedFunction [dbo]. [fun] Script Date: 11/22/2011 16:09:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo]. [fun] (@ type nvarchar (10 ))
Returns nvarchar (200)
As
Begin
Declare @ re nvarchar (200)
Declare @ code nvarchar (200)
Set @ re =''
Set @ code =''
Select @ re = @ re + productCode + ', 'From T where type = @ type group by productCode
Select @ re = left (@ re, len (@ re)-1)
Return @ re
End

3. query statement
Select type, dbo. fun (type) from (select distinct type from T)
Result:
Pen 0004,0005
Pencil 0001,0002, 0003
Ballpoint pen 0007,0008