For example, in stead of:

ProjectID                    Label
————                   ——–
1200                           label1
1200                           label2
1200                           label3

I would like the result of my query to look like this:
ProjectID                    Label
————                   ——–
1200                          label1, label2, label3

In SQL Server 2000 this isn’t such a straightforward thing to do. After some googling, I came across a very useful forumthat helped me out. I can use a function to return the list of labels:

USE myDatabaseName
GO
CREATE FUNCTION dbo.ConcatLabels(@projectID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+’, ‘, ”) + CONVERT(varchar(20), b.label_name)
FROM dbo.[T_labelproject] a
JOIN dbo.T_label b ON b.labelID = a.labelID
WHERE a.projectID = @projectID
ORDER BY b.label_Name
RETURN @Output
END
GO

Query it with:
SELECT projectID, dbo.ConcatLabels(projectID) AS Label FROM T_Project