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
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 | Photo_ID Tags 1 Beach,Sand 2 Mountain,Waterfall 3 Island,White Sand,Blue waters |
Script to create sample data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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
1 2 3 4 5 6 7 8 9 10 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 |