开发者

T-SQL: How to dynamize this Join-Function?

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, del开发者_如何学JAVAimiter, relation-table-names and the destination-column-name.

For example this datamodel(the function actually doesn't need to know tabData, only for completeness added here):

T-SQL: How to dynamize this Join-Function?

The static scalar-valued-function i want to dynamize is:

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

This function simply concatenates rows to one varchar-value separated with a delimiter, for example '0345:0550:0700:1230' where : is the separator.

Edit 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 String.Join(separator,array) in programming).

Here is the Split-UDF for the sake of completeness:

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) > 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  

Thank you in advance :)


May be this can help:

Create FUNCTION [dbo].[StringJoin]
(
    @Seperator      char,
    @Array          varchar(200)
)
RETURNS nvarchar(256)
AS
BEGIN
    DECLARE
    @rv         varchar(200)
    select @rv=REPLACE(@Array,@Seperator,'')    
    RETURN @rv
END
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜