请教一个sql语句

Question

CREATE FUNCTION  dbo.f_string(@name1 varchar(10))
RETURNS varchar(2000)---返回值
asdeclare @ varchar(2000)
set @=''
select @=@+name2+',' from table1 where name1=@name1
set  @=left(@,len(@)-1)
RETURN(@)

Answer 1

drop table table1
create table table1(id int, name1 varchar(10), name2 varchar(20))
insert table1 values(1,  1,     1)
insert table1 values(2,  1,     2)
insert table1 values(3,  1,     3)
insert table1 values(4,  1,     4)
insert table1 values(5,  2,     8)
insert table1 values(6,  3,     9)
insert table1 values(7,  3,     10)
insert table1 values(8,  3,     11)
insert table1 values(9,  4,     12)
 
CREATE FUNCTION  dbo.f_string(@name1 varchar(10))
RETURNS varchar(2000)---返回值
as
begin
declare @ varchar(2000)
set @=''
select @=@+name2+',' from table1 where name1=@name1
set  @=left(@,len(@)-1)
RETURN(@)
endselect  name1, dbo.f_string(name1) from (select distinct name1 from table1) a
name1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1          1,2,3,4
2          8
3          9,10,11
4          12

Answer 2

有没有不用函数的方法呢.
而只有select查询语句来实现.to caiyunxia(monkey) 
谢谢.
但是我想尽量避免使用函数.

Answer 3

create table table1(id int, name1 varchar(10), name2 varchar(20))
insert table1 values(1,  1,     1)
insert table1 values(2,  1,     2)
insert table1 values(3,  1,     3)
insert table1 values(4,  1,     4)
insert table1 values(5,  2,     8)
insert table1 values(6,  3,     9)
insert table1 values(7,  3,     10)
insert table1 values(8,  3,     11)
insert table1 values(9,  4,     12)
SELECT DISTINCT NAME1,NAME2,CAST('' AS VARCHAR(2000)) NEW_NAME INTO # FROM TABLE1
DECLARE @NAME varchar(10),@n varchar(2000)
set @n='' 
update # set @n=case when name1<>@NAME then name2+',' else @n+name2+',' end,@NAME=name1,NEW_NAME=@n 
select name1,max(NEW_NAME) from # group by  name1
drop table table1,#
name1                                                                                                         
1          1,2,3,4,
2          8,
3          10,11,9,
4          12,

Answer 4

--用临时表的处理方法
select name1,name2,sid=(
select sum(1) from 表 where name1=a.name1 and id<=a.id)
into #t from 表 a
where (select sum(1) from 表 where name1=a.name1 and id<=a.id)<=4select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then '' 
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then '' 
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' endfrom(
select name1,name2 from #t where sid=1
)a left join(
select name1,name2 from #t where sid=2
)b on a.name1=b.name1 left join(
select name1,name2 from #t where sid=3
)c on a.name1=c.name1 left join(
select name1,name2 from #t where sid=4
)d on a.name1=d.name1

Answer 5

--直接生成结果的方法
select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then '' 
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then '' 
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' end
from(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=1
)a left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=2
)b on a.name1=b.name1 left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=3
)c on a.name1=c.name1 left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=4
)d on a.name1=d.name1

Answer 6

--测试--测试数据
create table 表(id int,name1 int,name2 int)
insert 表 select 1,1,1
union all select 2,1,2
union all select 3,1,3
union all select 4,1,4
union all select 5,2,8
union all select 6,3,9
union all select 7,3,10
union all select 8,3,11
union all select 9,4,12
go--直接生成结果的方法
select a.name1,name2=cast(a.name2 as varchar)
+case when b.name2 is null then '' 
else ','+cast(b.name2 as varchar) end
+case when c.name2 is null then '' 
else ','+cast(c.name2 as varchar) end
+case when d.name2 is null then '' else '...' end
from(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=1
)a left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=2
)b on a.name1=b.name1 left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=3
)c on a.name1=c.name1 left join(
select name1,name2 from 表 a 
where (
select sum(1) from 表 
where name1=a.name1 and id<=a.id
)=4
)d on a.name1=d.name1go--删除测试环境
drop table 表/*--测试结果
name1       name2          
----------- ---------------
1           1,2,3...
2           8
3           9,10,11
4           12（所影响的行数为 4 行）
--*/

Answer 7

to zjcxc &  caiyunxia(monkey) 呵呵,多谢你们.今天头晕脑胀的,也不知是不是天气关系.
无心写代码,在网上瞎转了半天,
总算下班了:)仔细考虑了一下,觉得这样大量的select的话,效率太低
还是打算用
select id,name1,name from table order by name1
这样来做,
然后自己在代码中判断name1是否相同.
这样感觉应该速度上应该快点.