求SQL 按字段 求字符串的和 Group by 解决就揭贴在线等谢谢

[tammy2net]

表结构
A
------------------
apple 5
apple 6
apple 7
pear 1
pear 2
pear 3
--------------------



要得到TABE B
--------------------
apple 5,6,7
pear 1,2,3
--------------------

[tammy2net]

如果 SQL 写不出来 ， 麻烦帮我写个存储过程游标的 也行啊
谢谢了

[talantlee]

drop table a

----測試環境 --------將同組的字段用逗號連接起來
create table a(id1 int,id2 varchar(50) )
insert into a
select 2, 1 union all
select 2 , 2 union all
select 2 , 3 union all
select 2 ,3 union all
select 2 , 5 union all
select 3 , 2 union all
select 3 , 3 union all
select 3, 5 union all
select 3 , 5
if exists (select 1 from sysobjects where name='combine' and xtype='fn')
drop Function combine
------編寫函數實現整合功能
create FUNCTION combine(@vchA varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r=''
SELECT @r=@r+','+id2 FROM a WHERE id1=@vchA
RETURN(substring(@r,2,8000))
END
GO
-------執行語句
select id1,id2=dbo.combine(id1) from a group by id1
----刪除測試環境
---drop function combine
---drop table a
-------create by talantlee---------
--------------逗號問題的還原問題----
----------------------------
---測試環境
--要求從--表 a(id1,id2)
drop table a,b,#a
create table a (id1 int,id2 varchar(100))

insert into a
select 2, ' 1,2,3' union all
select 2, '3,5' union all
select 3, '2,3,5' union all
select 3, '5'

--轉化成為表b
id1 id2
2 1
2 2
2 3
2 3
2 5
3 2
3 3
3 5
3 5

----建立必須條件
select * into b from a where 1<>1 ---復制表結構到b
select * into #a from a ---把數據導入虛擬表



----循環取結果
declare @a int
set @a=1
while(@a<>0)
begin

insert into b select * from #a where patindex('%,%',id2)=0

delete from #a where patindex('%,%',id2)=0

insert into b select id1,left(id2, patindex('%,%',id2)-1) from #a
update #a set id2=stuff(id2,1,patindex('%,%',id2),'')
select @a=count(*) from #a
end

---得到結果
select * from b order by id1 ---b表為所求
----刪除測試環境
drop table #a,a,b

[tammy2net]

你写的好难 我回去研究哈 搞明白了 再来揭贴顶哈 不好意思了 先下班 祝大家工作愉快。。。

[tdtjjiao]

--加入测试数据
create table #t (id int Identity(1,1), a varchar(10), b int)
insert #t (a, b) values ('apple', 5)
insert #t (a, b) values ('apple', 6)
insert #t (a, b) values ('apple', 7)
insert #t (a, b) values ('pear', 1)
insert #t (a, b) values ('pear', 2)
insert #t (a, b) values ('pear', 3)
create table #t1 (id int Identity(1,1), a varchar(10), b varchar(8000) not null, c int)
Insert #t1 (a, b, c) select a, '', count(*) from #t group by a
declare @count int, @min int, @max int, @a varchar(10), @min1 int, @max1 int
set @min = 1
set @min1 = 1
select @max=Max(id) from #t1
while @min <= @max
begin
Select @a = a from #t1 where id = @min order by id
Select @max1 = max(id) from #t where a = @a
while @min1 <= @max1
begin
if @min1 < @max1
begin
update #t1 set #t1.b = #t1.b + convert(varchar(1), s.b)+',' from #t s where s.id = @min1 and #t1.a = @a
end
if @min1 = @max1
begin
update #t1 set #t1.b = #t1.b + convert(varchar(1), s.b) from #t s where s.id = @min1 and #t1.a = @a
end
set @min1 = @min1 + 1
end
set @min = @min + 1
end
--结果
select id, a, b from #t1
id a b
1 apple 5,6,7
2 pear 1,2,3

--删除测试

drop table #t, #t1

[lengxiaowei]

create table t (id int Identity(1,1), a varchar(10), b int)
insert t (a, b) values ('apple', 5)
insert t (a, b) values ('apple', 6)
insert t (a, b) values ('apple', 7)
insert t (a, b) values ('pear', 1)
insert t (a, b) values ('pear', 2)
insert t (a, b) values ('pear', 3)

alter function jionstr (@str varchar(50))
returns varchar(100)
as
begin
declare @str1 varchar(100)
set @str1=''
select @str1=@str1+cast(b as varchar(10))+',' from t where a=@str
return @str1
end

select a,dbo.jionstr(a) from t group by a

[tammy2net]

lengxiaowei(小伟) 结果：
id a b
1 apple ,5,6,7
2 pear 1,2,3,
使用了dbo.jionstr(a)，很正确
---------------------------------------
tdtjjiao(给分) 结果：
id a b
1 apple 5,6,7
2 pear 1,2,3
只用SQL 方法最简单最 实用 个人比较喜欢这样的
-----------------------------------------

talantlee(為了她，再努力點！！！) 简单函数方法的结果是：

id a b
1 apple ,5,6,7
2 pear 1,2,3,

使用了dbo.，很正确
解答的方法很全具有学习研究价值。（偶还没来的及研究你所有代码 忙 汗```）

[tammy2net]

好帖 揭

[tammy2net]

谢谢大家

[apple_001]

虽然结贴了mark一下