sql中拼接字符串的问题

[ShaoWenJing]

数据结构：
id name Num
1 aaaaaa Num1
2 aaaaaa Num2
3 aaaaaa Num3
4 aaaaaa Num4
5 aaaaaa Num5
6 aaaaaa Num6
7 bbbbbb Num7
8 bbbbbb Num8
9 bbbbbb Num9
10 bbbbbb Num10
11 bbbbbb Num11
12 bbbbbb Num12
13 bbbbbb Num13

想的到的结果：
aaaaaa Num1，Num2，Num3
aaaaaa Num4，Num5，Num6
bbbbbb Num7, Num8, Num9
bbbbbb Num10,Num11,Num12
bbbbbb Num13

也就是相同的名字后面的Num最多拼3个


测试数据

CREATE TABLE [dbo].[tb2](
[id] [int] NULL,
[name] [varchar](50) NULL,
[num] [varchar](50) NULL
)
insert into tb2(id,name,symbol)
select 1,'aaaaaa','Num1'
union
select 2,'aaaaaa','Num2'
union
select 3,'aaaaaa','Num3'
union
select 4,'aaaaaa','Num4'
union
select 5,'aaaaaa','Num5'
union
select 6,'aaaaaa','Num6'
union
select 7,'bbbbbb','Num7'
union
select 8,'bbbbbb','Num8'
union
select 9,'bbbbbb','Num9'
union
select 10,'bbbbbb','Num10'
union
select 11,'bbbbbb','Num11'
union
select 12,'bbbbbb','Num12'
union
select 13,'bbbbbb','Num13'

[dawugui]

--2000用如下函数.

create function dbo.f_str(@name varchar(20) , @px int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(Num as varchar) from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) m where name = @name and px = @px
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select name , num = dbo.f_str(name,px) from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) m group by name , px

[dawugui]

CREATE TABLE tb(
[id] [int] NULL,
[name] [varchar](50) NULL,
[num] [varchar](50) NULL
)
insert into tb(id,name,num)
select 1,'aaaaaa','Num1'
union
select 2,'aaaaaa','Num2'
union
select 3,'aaaaaa','Num3'
union
select 4,'aaaaaa','Num4'
union
select 5,'aaaaaa','Num5'
union
select 6,'aaaaaa','Num6'
union
select 7,'bbbbbb','Num7'
union
select 8,'bbbbbb','Num8'
union
select 9,'bbbbbb','Num9'
union
select 10,'bbbbbb','Num10'
union
select 11,'bbbbbb','Num11'
union
select 12,'bbbbbb','Num12'
union
select 13,'bbbbbb','Num13'
go

create function dbo.f_str(@name varchar(20) , @px int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(Num as varchar) from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) m where name = @name and px = @px
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select name , num = dbo.f_str(name,px) from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) m group by name , px


drop function dbo.f_str
drop table tb

/*
name                                               num                
-------------------------------------------------- -------------------
aaaaaa                                             Num1,Num2,Num3
aaaaaa                                             Num4,Num5,Num6
bbbbbb                                             Num7,Num8,Num9
bbbbbb                                             Num10,Num11,Num12
bbbbbb                                             Num13

（所影响的行数为 5 行）
*/

[dawugui]

--sql 2005如下:

select name, [num] = stuff((select ',' + [num] from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) n where name = m.name and px = m.px for xml path('')) , 1 , 1 , '')
from (select t.* , px = (((select count(1) from tb where name = t.name and id < t.id) + 1) - 1) / 3 from tb t) m
group by name,px

[SQL77]

drop table tb2


CREATE TABLE [dbo].[tb2](
[id] [int] NULL,
[name] [varchar](50) NULL,
[num] [varchar](50) NULL
)
insert into tb2(id,name,num)
select 1,'aaaaaa','Num1'
union
select 2,'aaaaaa','Num2'
union
select 3,'aaaaaa','Num3'
union
select 4,'aaaaaa','Num4'
union
select 5,'aaaaaa','Num5'
union
select 6,'aaaaaa','Num6'
union
select 7,'bbbbbb','Num7'
union
select 8,'bbbbbb','Num8'
union
select 9,'bbbbbb','Num9'
union
select 10,'bbbbbb','Num10'
union
select 11,'bbbbbb','Num11'
union
select 12,'bbbbbb','Num12'
union
select 13,'bbbbbb','Num13'

alter  FUNCTION GET_STRING(@name varchar(10),@idd int)
RETURNS VARCHAR(100)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(100)
       SELECT @CLASS=ISNULL(@CLASS+',','')+LTRIM(num) 
	FROM tb2    WHERE name=@name and (id-1)/3=@idd
       RETURN @CLASS
  END
select name,dbo.get_string(name,idd) num 
from(select (id-1)/3 idd,name,num from  tb2 ) t
group by idd,name
name                                               num
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
aaaaaa                                             Num1,Num2,Num3
aaaaaa                                             Num4,Num5,Num6
bbbbbb                                             Num7,Num8,Num9
bbbbbb                                             Num10,Num11,Num12
bbbbbb                                             Num13

(5 行受影响)

[andybang1981]

学习了

[ShaoWenJing]

谢谢两位的详细回复！