Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Concatenate Multiple Records Into One Field

    Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

    TABLE
    ColumnA ColumnB
    1.......A
    2.......B
    3.......C
    4.......A
    5.......A
    6.......B
    7.......C
    8.......D
    9.......C
    10......E

    EXPECTED OUTPUT
    ColumnA ColumnB
    1.......4,5
    2.......6
    3.......7
    4.......1,5
    5.......1, 4
    6.......2
    7.......3
    8.......
    9.......3,7
    10......

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216

    Post

    create function ConcatFld (@RowId int, @RowVal char(1))
    returns varchar(100) AS
    begin
    declare @Ret varchar(100)
    set @Ret=''
    select @Ret= @Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@RowVal and ColA<>@RowId
    if len(@Ret) > 0
    set @Ret = left(@Ret,len(@Ret)-1)
    return @Ret
    end
    -----------------------

    select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Thanks Upalsen, this is exactly what I need!!!

Posting Permissions

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