How do I build a comma delimited string om SQL Server 2000?

I have a query that returns a reasonable number of records from a table. I need to include a comma delimited string as an output column. Something like this

SELECT
 column1,
 column2,
 column3,
 [Delimited string based on the id]
FROM
  sometable
WHERE
  id = someid

I know you can use the coalesce 开发者_如何学JAVAfunction which i have used in the past but im not sure how to integrate it into a select statement, also not really sure on the performance?

Any ideas?

I'd loop through the items to build the string with, then add it to the result set.

-- Minor performance tweak.
SET NOCOUNT ON
GO

-- ID of the item or widget or whatever 
DECLARE @someid int    

DECLARE @LookupItems TABLE
(
    ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
    LookupTableID int,          -- Primary key of the lookup table.
    LookupField varchar(30)     -- Text of the lookup value.
)

-- Update to the desired id.
SET @someid = 1234

INSERT INTO @LookupItems (ID, LookupField)
SELECT ID, Value
FROM dbo.somelookuptable
WHERE ID IN (

    SELECT lookupid
    FROM dbo.sometable
    WHERE ID = @someid

)

DECLARE 
    @Count int, 
    @Max int, 
    @DelimitedString varchar(1000)

-- Initialize with a non-NULL value to facilitate concatenation.
SET @DelimitedString = ''  

SET @Count = 1
SELECT @Max = MAX(ID) FROM @LookupItems

WHILE (@Count <= @Max)
BEGIN

    SELECT @DelimitedString = @DelimitedString + IsNull(somefield, '') + ','
    FROM @LookupItems
    WHERE ID = @Count

    SET @Count = @Count + 1

END

SELECT
    column1,
    column2,
    column3,
    @DelimitedString 
FROM dbo.sometable
WHERE id = someid