use coalesce function to concat field values

Problem: I got 3 tables: [Posts],[Tags] and [PostsTags]
When the tables join, I got multiple records for each post with distinct tags.
I want a table that contains single record for each post with tags concatenated.

Solution:

  1. In SQL Server 2005, I created a Scalar-valued Function that takes one parameter (the post id). This function returns concatenated values of the tag name given the post id.
  2. Then I called this function in the Views and Stored Procedure.
  3. The Scalar-valued Function is defined as below:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[SVF_TagNameByPostID] (@post_id int)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000)

	SELECT @Result = COALESCE(@Result + '|', '') +
	CAST(Table_Tags.tag_name AS nvarchar(50))
	FROM Table_Tags INNER JOIN
	Table_PostsTags ON Table_Tags.tag_id = Table_PostsTags.tag_id INNER JOIN
	View_Posts ON Table_PostsTags.post_id = View_Posts.post_id
	WHERE View_Posts.post_id = @post_id

	RETURN @Result
END
Advertisements

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: