Please help me, how to do his - SQL query (SQL Server 2000)

I have 开发者_如何学编程data like this:

ID   Name         Date
1    kishore      18-jan-2010
2    kishh        19-jan-2010
3    kii          NULL
4    kk           null
5    k            19-jan-2010

I should get a result like this:

ID   Name           Date
1    kishore        18-jan-2010
2    kishh,kii,kk   19-jan-2010
5    k              19-jan-2010

---

First, you create a function which takes an ID and returns the string you're after...

CREATE FUNCTION dbo.StringFromID(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @value varchar(8000)
    DECLARE @date datetime
    SELECT @date = [date], @value = [name] 
    FROM test_data WHERE [id] = @id

    WHILE EXISTS (  SELECT * FROM test_data 
            WHERE [id] = @id + 1 
            AND [date] IS NULL)
    BEGIN
        SELECT  @value = @value + ',' + [name]
        FROM test_data
        WHERE [id] = @id + 1

        SELECT @id = @id + 1
    END
    RETURN @value
END

Then you call that function and display your results.

SELECT [id], dbo.StringFromID(t.[id]) 'Name', [date]
FROM test_data t
WHERE [date] IS NOT NULL

---

Next time, write out the desired sort criteria in addition to example. Try this:

select [id], [name], [date] from MyTable
where [date] is not null
order by [date]