Concatenate field based on unique id.

• girija-265903

  SSC-Addicted

  Points: 436

  More actions

  November 4, 2005 at 10:40 am

  #89822

  I have a table like this

  Id comment

  -- -------

  1 abcd 

  1 efgh

  1 cc

  2 aa1223

  3 myComment

  3 myComm123444444

  I want to a SQL statement that returns unique Ids with their comments concatenated with comma.

  1 abcd,efgh,cc

  2 aa1223

  3 myComment,myComm123444444

  Can anyone help me with this.

  thanks.

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  November 4, 2005 at 11:17 am

  #602413

  Create an inline function

  -- =============================================

  -- Create scalar function (FN)

  -- =============================================

  IF EXISTS (SELECT *

      FROM   sysobjects

      WHERE  name = N'fn_ConcatStrings')

   DROP FUNCTION fn_ConcatStrings

  GO

  CREATE FUNCTION fn_ConcatStrings

   (@ID int)

  RETURNS varchar(500)

  AS

  BEGIN

  declare @String varchar(500)

  select @String = coalesce(@String,'') + Comment + ','

  from mytable

  where [Id] = @ID

  if Right(@String,1) = ','

      set @String = substring(@String,1,len(@String) -1)

  return @String

  END

  GO

  then execute

  select ID, dbo.fn_ConcatStrings(ID)

  From (Select distinct ID from Mytable) as DT