Show multiple rows in one column in SQL

Some websites still use a rather old database to store their data. The one I'm currently facing is MS SQL Server 2000.

I've been looking for a way to show multiple rows in one column, one cell. The content of it separated by comma's.

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 <a title="SQLTeam" href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293" target="_blank">forum</a> that 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

2014-10-06 13:57:40


< Back