Creating a Delimited list from a column of a table

Many times when we normalize our database, we end up storing multi-values attributes as separate rows in table. For eg. Consider we are storing Photos and a set of tags associated with the photos.

Assume the following table structure

Photo_ID   Tag
  1	   Beach
  1	   Sand
  2	   Mountain
  2	   Waterfall
  3	   Island
  3	   White Sand
  3	   Blue waters

Now while retrieving the tags for photos we need only one record per photo with all the tags as comma separated in the output.

Photo_ID	Tags
  1	        Beach,Sand
  2	        Mountain,Waterfall
  3	        Island,White Sand,Blue waters

Script to create sample data

IF  EXISTS (SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Photo_Tags]')
                    AND type in (N'U')
            )
BEGIN
    DROP TABLE [dbo].[Photo_Tags]
END

GO
CREATE TABLE [dbo].[Photo_Tags]
(
    Photo_ID    INT
    ,Tag        VARCHAR(256)
)
GO
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 1,'Beach'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 1,'Sand'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 2,'Mountain'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 2,'Waterfall'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'Island'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'White Sand'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'Blue waters'

Solution In SQL Server 2005 Onwards

DECLARE @delimiter VARCHAR(10)
SET @delimiter ='|'
SELECT Photo_ID,
    REPLACE(REPLACE((SELECT
        REPlACE(Tag,' ','#@#') AS 'data()'
     FROM [Photo_Tags] t2
     WHERE t2.Photo_ID = t1.Photo_ID
     FOR XML PATH('')),' ',@delimiter),'#@#',' ') AS Tags
FROM [Photo_Tags] t1
GROUP BY Photo_ID

Notice the Innermost REPLACE function REPlACE(Tag,’ ‘,’#@#’) is to escape spaces in the tag values, since the 2nd REPLACE function is replacing space with the delimiter. Hence we need the Outermost REPLACE function to replace the escape sequence with a space.

Solution In SQL Server 2000

In SQL Server 2000, we need to create a function which would accept photo_id as input param and as output would generate the delimited string of tags for that photo_id.

IF  EXISTS (SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[FN_Get_Tags_For_Photo]')
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
            )
BEGIN
    DROP FUNCTION [dbo].[FN_Get_Tags_For_Photo]
END
GO
CREATE FUNCTION [dbo].[FN_Get_Tags_For_Photo]
( @Photo_ID int )
RETURNS varchar(max)
AS
BEGIN
    DECLARE @Tags varchar(max)

    SELECT @Tags = COALESCE(@Tags + ', ', '') + Tag
    FROM [Photo_Tags]
    WHERE Photo_ID = @Photo_ID
    RETURN @Tags
END
GO
SELECT  Photo_ID ,dbo.FN_Get_Tags_For_Photo(Photo_ID)
FROM    [Photo_Tags]
GROUP BY Photo_ID

Cleanup script

IF  EXISTS (SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Photo_Tags]')
                    AND type in (N'U')
            )
BEGIN
    DROP TABLE [dbo].[Photo_Tags]
END
GO
IF  EXISTS (SELECT *
            FROM sys.objects
            WHERE object_id = OBJECT_ID(N'[dbo].[FN_Get_Tags_For_Photo]')
            AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
            )
BEGIN
    DROP FUNCTION [dbo].[FN_Get_Tags_For_Photo]
END
GO