SQL Server Function That Returns A Delimited String

Question

Hey I have a table like this:

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?

Solution 1

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 )

Solution 2

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.

Comments

[Aditya T]

And how would I put this in a function, that returns that string?

[Aditya T]

And how would I put this in a function, that returns that string?