有一个表test,有ID,Num,Name,ItemName,ItemValue,ItemDesc这些字段
表中有如下数据:
ID Num Name ItemName ItemValue ItemDesc ItemDate
1 001 a1 b1 c1 d1 2008-12-23
2 002 a2 b2 c2 d2 2008-12-25
2 003 a3 b2 c2 d3 2008-12-26
2 004 a4 b2 c2 d4 2008-12-29
5 005 a5 b5 c5 d5 2008-12-27 现在,我想SELECT的时候,把其中ID,ItemName,ItemValue值相同的整合为一条数据,最后显示的结果如下所示:1 001 a1 b1 c1 d1 2008-12-23
2 002/003/004 a2/a3/a4 b2 c2 d2/d3/d4 2008-12-25/2008-12-26/2008-12-29
5 005 a5 b5 c5 d5 2008-12-27
表中有如下数据:
ID Num Name ItemName ItemValue ItemDesc ItemDate
1 001 a1 b1 c1 d1 2008-12-23
2 002 a2 b2 c2 d2 2008-12-25
2 003 a3 b2 c2 d3 2008-12-26
2 004 a4 b2 c2 d4 2008-12-29
5 005 a5 b5 c5 d5 2008-12-27 现在,我想SELECT的时候,把其中ID,ItemName,ItemValue值相同的整合为一条数据,最后显示的结果如下所示:1 001 a1 b1 c1 d1 2008-12-23
2 002/003/004 a2/a3/a4 b2 c2 d2/d3/d4 2008-12-25/2008-12-26/2008-12-29
5 005 a5 b5 c5 d5 2008-12-27
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @RE VARCHAR(100)
SET @RE = ''
SELECT @RE = @RE+Num+'/' FROM Test WHERE ID = @ID1
RETURN RTRIM(@RE)
END GOselect distinct ID,dbo.Test_Func(ID) from Test
CREATE FUNCTION getnumlist
(@id int)
RETURNS varchar(2000)
AS
BEGIN
declare @b1 varchar(2000)
set @b1 = ''
select @b1=@b1+num+'/' from tb where id=@id
set @b1=left(@b1,len(@b1)-1)
return @b1END
我会好好研究一下.