SQL数据库合并记录问题 ( 积分: 50 )

一表内容为
1 A
1 B
1 C
2 D
2 E
要实现成如下
1 A,B,C
2 D,E

2. 行列转换--合并

有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1

创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A

合并的函数
写得有点问题
其实可以用一个存储过程来解决的

DROP TABLE TABLE1
GO
CREATE TABLE TABLE1
( ID INT NULL,
PID INT NULL
)
GO
INSERT INTO TABLE1 VALUES(1,1)
INSERT INTO TABLE1 VALUES(1,2)
INSERT INTO TABLE1 VALUES(1,3)
INSERT INTO TABLE1 VALUES(2,4)
INSERT INTO TABLE1 VALUES(2,5)
INSERT INTO TABLE1 VALUES(2,6)
INSERT INTO TABLE1 VALUES(3,7)
INSERT INTO TABLE1 VALUES(3,8)
GO
DROP FUNCTION FMERG
GO
CREATE FUNCTION FMERG(@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STR VARCHAR(8000)
SET @STR=''
SELECT @STR=@STR+','+CAST(PID AS VARCHAR) FROM TABLE1 WHERE ID=@ID
SET @STR=RIGHT(@STR,LEN(@STR)-1)
RETURN(@STR)
END
GO
SELECT DISTINCT ID,DBO.FMERG(ID) FROM TABLE1