合并结果

ID    VALUE1    VALUE2
1      a          0.1
1      b          0.2
1      c          0.3
2      a          0.1
2      c          0.3

现在我想取得的结果是
ID    result1     result2
1     a、b、c     0.1、0.2、0.3
2     a、c        0.1、0.3
其中value1和value2的个数是不定的，只能用一个sql语句实现

**********************************************************************************

create function f_str(@id varchar(10))
returns varchar(500)
as
begin
    declare @s varchar(500)
    set @s=''
    select @s=@s+'、'+rtrim(value2) from table where id=@id
     return stuff(@s,1,2,'')
end
GO

create function f_str2(@id varchar(10))
returns varchar(500)
as
begin
    declare @s varchar(500)
    set @s=''
    select @s=@s+'、'+rtrim(value1) from table where id=@id
     return stuff(@s,1,2,'')
end
GO

select id,f_str2(id) as result1,f_str(id) as result2 from table where id in (select min(id) from table group by id)

**********************************************************************************

CREATE TABLE tb([编号] int, [课程] varchar(100), [分数] int)
INSERT INTO tb
SELECT 1, 'a', 80
UNION ALL SELECT 2, 'c', 85
UNION ALL SELECT 1, 'b', 90
UNION ALL SELECT 2, 'b', 60
UNION ALL SELECT 3, 'a', 70

GO

CREATE FUNCTION GetNew(@Code INT, @Flg TINYINT)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @re VARCHAR(10)
SET @re = ''
IF @Flg = 1
SELECT @re = @re + 课程 + '、' FROM tb WHERE 编号 = @Code
ELSE IF @Flg = 2
SELECT @re = @re + CAST(分数 AS VARCHAR) + '、' FROM tb WHERE 编号 = @Code
RETURN LEFT(@re, LEN(@re) - 1)
END
GO

SELECT DISTINCT 编号, dbo.GetNew(编号, 1) AS 课程, dbo.GetNew(编号, 2) AS 分数 FROM TB

DROP FUNCTION GetNew
DROP TABLE TB