Concatenate Column

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  February 27, 2006 at 6:27 pm

  #111708

  I have the following data. How do I concatenate the UserText Column to produce only 1 row for each UserID?

  UserID              UserText

  1                     ABC

  1                     DEF

  1                     GHI

  2                     ABC

  2                     DEF

  2                     GHI

   

  ---

  Kindest Regards,

• brendt hess

  Hall of Fame

  Points: 3256

  More actions

  February 27, 2006 at 8:12 pm

  #623581

  IMO, this calls for a User-defined function:

  -- Create a testing table

  Create Table TestTable (ID int, TestStr varchar(20))

  GO

  Insert Into TestTable Values (1, 'ABC')

  Insert Into TestTable Values (1, 'DDD')

  Insert Into TestTable Values (1, 'ZZZ')

  Insert Into TestTable Values (2, 'NOX')

  Insert Into TestTable Values (2, 'IOU')

  Insert Into TestTable Values (2, 'S')

  GO

  -- Create the UDF.  This uses the Concatenation on select trick

  Create Function dbo.ufn_ConcatTestTable

   (@ID int)

  RETURNS varchar(256)

  AS

  BEGIN

     Declare @Work varchar(256)

     Set @Work = ''

     Select @Work = @Work + TestStr + ', '

     FROM TestTable

     WHERE [id] = @ID

     IF Len(@Work) > 0 Set @Work = Left(@Work, Len(@Work) - 1)

     Return @Work

  END

  GO

  -- And the test

  Select Test.ID, dbo.ufn_ConcatTestTable(Test.ID) As List

  From

   (Select TestTable.ID

   From TestTable

   Group By TestTable.ID) Test

  --- Results ---

  ID              List

  ----------- -----------------------------------------------------------

  1               ABC, DDD, ZZZ

  2               NOX, IOU, S

   

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  February 27, 2006 at 10:40 pm

  #623589

  That works well. Thanks.

  ---

  Kindest Regards,