title: T-SQL: How to dynamize this Join-Function?
body: <p>i have following UDF in SQL-Server 2005. It's joining a Table and concatenates all rows with a separator to one scalar value. Because i need it for other tables too, i wondered how to make it dynamic so that it takes the FK, delimiter, relation-table-names and the destination-column-name. </p>
  
  <p>For example this datamodel(the function actually doesn't need to know tabData, only for completeness added here):
  <img src="https://i.stack.imgur.com/qtaJz.jpg" alt="enter image description here"></p>
  
  <p>The static scalar-valued-function i want to dynamize is:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[getTabDataSymptomCodes]
  (
      @idData Int,
      @delimiter varchar(5)
  )
  RETURNS VARCHAR(8000)
  AS
  BEGIN
      DECLARE @Codes VARCHAR(8000) 
      SELECT   @Codes = COALESCE(@Codes + @delimiter, '') + tdefSymptomCode.SymptomCodeNumber
      FROM    trelData_SymptomCode INNER JOIN
              tdefSymptomCode ON trelData_SymptomCode.fiSymptomCode = tdefSymptomCode.idSymptomCode
      WHERE     (trelData_SymptomCode.fiData = @idData)
      ORDER BY tdefSymptomCode.SymptomCodeNumber
      return @Codes
  END
  </code></pre>
  
  <p>This function simply concatenates rows to one varchar-value separated with a delimiter, for example <code>'0345:0550:0700:1230'</code> where <code>:</code> is the separator.</p>
  
  <p><strong>Edit</strong> for clarification:
  I have already a UDF that splits a given varchar separated by a char into a table(f.e. '1,2,3,4' into separate rows). Now i need the opposite(like <code>String.Join(separator,array)</code> in programming). </p>
  
  <p>Here is the Split-UDF for the sake of completeness:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[Split]
  (
      @ItemList NVARCHAR(MAX), 
      @delimiter CHAR(1)
  )
  RETURNS @IDTable TABLE (Item VARCHAR(50))  
  AS      
  
  BEGIN    
      DECLARE @tempItemList NVARCHAR(MAX)
      SET @tempItemList = @ItemList
  
      DECLARE @i INT    
      DECLARE @Item NVARCHAR(4000)
  
      SET @tempItemList = REPLACE (@tempItemList, ' ', '')
      SET @i = CHARINDEX(@delimiter, @tempItemList)
  
      WHILE (LEN(@tempItemList) &gt; 0)
      BEGIN
          IF @i = 0
              SET @Item = @tempItemList
          ELSE
              SET @Item = LEFT(@tempItemList, @i - 1)
          INSERT INTO @IDTable(Item) VALUES(@Item)
          IF @i = 0
              SET @tempItemList = ''
          ELSE
              SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
          SET @i = CHARINDEX(@delimiter, @tempItemList)
      END 
      RETURN
  END  
  </code></pre>
  
  <p>Thank you in advance :)</p>