Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: Aggregate string concatenation

    I have the following data:

    create table TempTable(name varchar(50), value varchar(50))
    insert into temptable values ('A', 'one')
    insert into temptable values ('A', 'two')
    insert into temptable values ('A', 'three')
    insert into temptable values ('B', 'four')
    insert into temptable values ('B', 'five')

    and i would like the following output:

    'A', 'one, two, three'
    'B', 'four, five'

    any ideas on how to accomplish this in Sql Server 2000?

    thx in advance..

  2. #2
    Join Date
    Oct 2005
    Posts
    2
    Never mind, i figured it out:

    CREATE FUNCTION dbo.TempFunction (@Name as varchar(50))
    RETURNS varchar(1000)
    AS
    BEGIN
    DECLARE @RetVal varchar(1000)
    SELECT @RetVal = ''
    SELECT @RetVal=@RetVal + value + ', '
    FROM temptable
    WHERE name=@name
    select @RetVal = left(@RetVal, len(@RetVal)-1)
    RETURN (@RetVal)
    END


    SELECT name, dbo.tempfunction(name) as [values]
    FROM temptable
    GROUP BY name;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •