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

    Unanswered: User Defined Function - Concatenation

    I'm a little stuck with a concatenation function that I am working on that concatenates multiple rows into a single column. I am trying to achieve the following results from the source data below.

    Expected Result
    ----------------
    IDNUM Column1
    1 Apple, Banana
    2 Orange
    5 Apple, Orange


    Source Table Data
    -------------------
    IDNUM Column1
    1 Apple
    2 Orange
    5 Apple
    5 Orange
    1 Banana


    Below is my function that I am having problems with. The function is currently repeating the same value in Column1 over and over again. Example
    IDNUM Column1
    1 Apple,Apple,Apple,Apple.........
    2 Orange, Orange,Orange,Orange.........
    .
    .
    .

    CREATE FUNCTION Concat (@ID varchar(100), @ColToConcat varchar(100))
    returns varchar(100) AS
    begin
    declare @Ret varchar(100)
    set @Ret=''
    select @Ret= @Ret + @ColToConcat +','
    from TableName
    where RecordID = @ID
    if len(@Ret) > 0
    set @Ret = left(@Ret, len(@Ret)-1)
    return @Ret
    end


    Can someone please help me fix my function, Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You cannot use dynamic column names like that. To prove, check this works:
    Code:
    ALTER FUNCTION Concat (@IDNUM int) 
    returns varchar(100) AS
    begin 
    declare @Ret varchar(100)
    set @Ret=''
    select @Ret= @Ret + Column1 +',' 
    from TableName
    where RecordID = @ID
    if len(@Ret) > 0
    set @Ret = left(@Ret, len(@Ret)-1)
    return @Ret
    end
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thats an interesting one

    you could try something like
    Code:
    CREATE FUNCTION [dbo].Combine
    (
    	@ID int,
    	@Matched table
    )  
    RETURNS @RtnValue varchar(200 
    AS  
    BEGIN 
    	set @RtnValue = ''
    	Declare @name 
    	select @name = Top 1 Column1
    	From TableName
    	where IDNUM = @ID and not Column1 in (select Column1 from @Matched
    
    	if @name is Not Null
    	begin
    		INSERT INTO @Matched
               (Column1)
    	     VALUES
               (@name)
    
    		@RtnValue = @name + Combine(@ID,@Matched)
    	end
    
    	Return
    END
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's this bit?
    Code:
    @RtnValue = @name + Combine(@ID,@Matched)
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    concat the first row to those that follow it, by using a recursive call
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    should have been dbo.Combine(@ID,@Matched)
    Last edited by m.timoney; 10-02-09 at 10:07.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Nov 2005
    Posts
    91
    Thanks for all your help guys it works!!!

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by dsmbwoy
    Thanks for all your help guys it works!!!
    you can do the same with a loop but after training in haskell i tend to think of recursion before looping
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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