7,732
社区成员




select id1,sum(id3 * 10^(id3-1)) as col
from table3
group by id1
id1 Expr1001
0022-0396_2008_244 6,5,4,3,2,1
0022-0531_2008_138 1
0022-0531_2008_139 1
select id1,replace(col,"0",",")
from (
select id1,sum(id3 * 100^(id3-1)) as col
from table3
group by id1) a
Id3 id1 id2
1 0022-0531_2008_138 0022-0396_2008_138_1
1 0022-0531_2008_139 0022-0396_2008_139_1
1 0022-0396_2008_244 0022-0396_2008_244_1
2 0022-0396_2008_244 0022-0396_2008_244_2
3 0022-0396_2008_244 0022-0396_2008_244_3
4 0022-0396_2008_244 0022-0396_2008_244_4
5 0022-0396_2008_244 0022-0396_2008_244_5
6 0022-0396_2008_244 0022-0396_2008_244_6
create table tb(id int, id1 varchar(20))
insert into tb values(0, '0022-0396_2008_244')
insert into tb values(1, '0022-0396_2008_244')
insert into tb values(2, '0022-0396_2008_244')
insert into tb values(3, '0022-0531_2008_138')
insert into tb values(4, '0022-0531_2008_139')
go
CREATE FUNCTION dbo.f_str(@val varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + cast(id as varchar) FROM tb WHERE id1 =@val
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt id1, dbo.f_str(id1) as id FROM tb GROUP BY id1