sql server多行数据拼接的实例方法

1.表结构

id type productCode

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