Return comma seperated list of sql result

Posted on 2007-08-02
Last Modified: 2008-01-09
I need a procedure (or function I guess) that will return as a string the values of a passed sql statement as comma seperated string.

In other words, I pass the string 'Select FirstName From Users Where DeptID = 3' and the returned string would be 'Joe, Fred, Wilma'

Is this possible?
Question by:UniqueData
    LVL 42

    Expert Comment

    declare @result varchar(1000)
    select @result=isnull(@result + ',' + FirstName, FirstName) from Users where DeptID=3
    LVL 7

    Author Comment

    That works great when the sql statement is fixed, but I need to be able to pass a sql statement to make the function more flexible.  This way I can use it for showing names from the Users table, Student List from the Student table, etc.
    LVL 68

    Expert Comment

    >> Is this possible? <<

    That depends.  If you are using SQL 2005, yes.  If you are using SQL 2000, yes if the total length of the output is > 8000 bytes [it can be done in SQL 2K for over 8K bytes, but that requires using TEXT columns, which are such a pain that I won't attempt to write it].

    It will have to be a stored proc, not a function, since you will need dynamic SQL.  Will post a possible solution ASAP.

    LVL 68

    Accepted Solution

    The procedure below is designed for a *single column* in the query.  You also cannot use TOP or DISTINCT.  Things like TOP and DISTINCT could be allowed most easily by adding a character that marked the beginning and end of the concat column [such as "SELECT ~FirstName~ FROM ..." or "SELECT TOP 10 DISTINCT ~UserName~ FROM ...."], but I didn't want to cloudy the query with that.

    CREATE PROCEDURE dbo.ConcatResults
        @sql NVARCHAR(4000)
    DECLARE @result VARCHAR(8000) --<<-- use VARCHAR(MAX) in SQL 2K5
    SET @sql = STUFF(@sql, CHARINDEX(N'SELECT ', @sql) + 7, 0,
        N'@result = ISNULL(@result + '', '', '''') + ')
    SET @sql = STUFF(@sql, CHARINDEX(N'FROM ', @sql) - 1, 0,
        N' + ''''')
    --PRINT @sql
    EXEC sp_executesql @sql, N'@result VARCHAR(8000) OUTPUT', @result OUTPUT
    SELECT @result

    EXEC dbo.ConcatResults 'Select FirstName From Users Where DeptID = 3'
    LVL 7

    Author Comment

    that worked PERFECTLY.  I do have sql 2000 so lets hope that I don't need more than 8000 characters (I wouldn't think so).

    I see where you are looking for the field and table names in @sql, but I just wish I knew what the heck the rest of the code is doing!!!
    LVL 68

    Expert Comment

    First, one quick correction, which technically doesn't hurt anything but is not needed.  This line can be removed:
    SET @sql = STUFF(@sql, CHARINDEX(N'FROM ', @sql) - 1, 0, N' + ''''')

    OK, now to what it's doing.  It's turning this:
    Select FirstName From Users Where DeptID = 3
    into this:
    Select @result = ISNULL(@result + ', ', '') + FirstName From Users Where DeptID = 3

    That will get the concatenated values into a variable [to prove it, you can declare @result, run the above code in qa, and add a PRINT @result to see the value of the variable).  

    But that variable still has to be gotten from the dynamic SQL back to the procedure code.  That's where sp_executesql comes in.  It ran return a value, but only if it's specified as an OUTPUT parameter on both the variable declaration(s) [parameter 2 to sp_exec] and the return variable(s) [params 3 thru however many vars you have].  Check the code in the stored proc and you'll see that is what was done.

    Here's another example:
    EXEC sp_executesql N'SELECT @date = GETDATE()', N'@date SMALLDATETIME OUTPUT', @date OUTPUT
    PRINT @date

    Contrast that with this (NOTE: this code causes an error):
    EXEC('SELECT @date = GETDATE()')

    In summary, the way SQL Server functions means:
    1) you have to use dynamic SQL for the main query -- since it is completely supplied by the user at run time.  
    2) You also have to modify the query to do concatenation of values, since that is the type of result set you want.  
    3) That requires using a variable.  In order to get the back the value of a variable from dynamic SQL, you must use sp_executesql.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now