1.表结构

idtypeproductCode

1铅笔0001

2铅笔0002

3铅笔0003

4钢笔0004

5钢笔0005

6钢笔0004

7圆珠笔0007

8圆珠笔0008

9圆珠笔0007

2.自定义函数fun

复制代码 代码如下:

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.查询语句

select type,dbo.fun(type) from (select distinct type from T) A

结果:

钢笔 0004,0005

铅笔 0001,0002,0003

圆珠笔 0007,0008