SQL Server Function That Returns A Delimited String

Hey I have a table like th开发者_C百科is:

ID  BitOne  BitTwo  BitThree
--  ------  ------  --------
99  NULL    1       NULL
99  1       NULL    NULL

And I need a user function that returns the string "BitTwo, BitOne" for the ID: 99 - which would be the input parameter for the function.

Is that possible?

Use [SOFDB1]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec SELECT dbo.GetString(7)  
ALTER FUNCTION dbo.GetString 
(   
    @ID BIGINT 
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @RESULT_STRING nvarchar(max);
    SELECT 
        @RESULT_STRING = CONVERT(nvarchar,(CASE
            WHEN t.BitOne IS NOT NULL THEN CONVERT(varchar,t.BitOne)
            ELSE 'NULL'
            END) + ',' +
        (CASE
            WHEN t.BitTwo IS NOT NULL THEN CONVERT(varchar,t.BitTwo)
            ELSE 'NULL'
            END) + ',' +
        (CASE
            WHEN t.BitThree IS NOT NULL THEN CONVERT(varchar, t.BitThree)
            ELSE 'NULL'
            END)) FROM TestTable1 t WHERE t.ID = @ID

    RETURN @RESULT_STRING
END

and call

in sp or something to get the value of that function >>>> SELECT dbo.GetString( theIDtoPass )

Function:

CREATE FUNCTION getList (
@id_parameter INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

  DECLARE @result VARCHAR(MAX)

  SELECT @result = MAX(CASE WHEN t.bitone = 1 THEN 'bitone' ELSE '' END) +','+
                   MAX(CASE WHEN t.bittwo = 1 THEN 'bittwo' ELSE '' END)
    FROM your_table t
   WHERE t.id = @id_parameter
GROUP BY t.id

 RETURN @result

END
GO

Use:

  SELECT MAX(CASE WHEN t.bitone = 1 THEN 'bitone' ELSE '' END) +','+
         MAX(CASE WHEN t.bittwo = 1 THEN 'bittwo' ELSE '' END)
    FROM TABLE t
   WHERE t.id = @id_parameter
GROUP BY t.id

But it'd help to know how you want to handle when an id value doesn't have a value for bitone, bittwo, etc. If both are NULL or not 1, then you'll still get a comma back as a result.

Well created a table with 4 fields ID - BigInt

BitOne - BigInt

BitTwo - BigInt

BitThree - BigInt

Sample data

1 1 NULL 1 >>> Output '1,NULL,1'

2 1 NULL NULL >>> Output '1,NULL,NULL'

3 NULL 1 NULL >>> Output 'NULL,1,NULL'

4 NULL 1 1 >>> Output 'NULL,1,1'

5 1 1 1 >>> Output '1,1,1'

QUERY >>>>>

SELECT 
    CONVERT(nvarchar,(CASE
        WHEN t.BitOne IS NOT NULL THEN CONVERT(varchar,t.BitOne)
        ELSE 'NULL'
        END) + ',' +
    (CASE
        WHEN t.BitTwo IS NOT NULL THEN CONVERT(varchar,t.BitTwo)
        ELSE 'NULL'
        END) + ',' +
    (CASE
        WHEN t.BitThree IS NOT NULL THEN CONVERT(varchar, t.BitThree)
        ELSE 'NULL'
        END)) AS RESULTSTRING from TestTable1 t WHERE t.ID = @ID

You can replace 'NULL' with '' to make empty string there or can put any string

Hope that helps

Regards, J'Sinh

Please try this solution.

DECLARE @Colors VARCHAR(MAX)
SELECT @Colors = ISNULL(@Colors + ', ', '') + cmnColor.Name
FROM  [dbo].[CMN_Sel_Colors] as cmnColor
INNER JOIN [dbo].[Sel_ItemColor] AS itemcolor ON itemcolor.ColorID = cmnColor.ColorID
WHERE itemcolor.ItemID = @ItemID

RETURN @Colors