Technical Article

Return Column as List of Comma Seperated Values

,

Need a comma seperated list of a particular column in a table.  This will do the trick.

CREATE    PROCEDURE ap_ReturnCSV  
@SQLstring nvarchar(4000), @ColumnName varchar(25), @list varchar(8000)  OUTPUT 
/*********************************************************************************************************
       NAME: ap_ReturnCSVSpecified by: Bryan Bain
       REQUESTED DATE: 14-OCT-2003Designed by:  Bryan Bain
Bryan@HillCountryVillage.com

Requirements:Returns a comma seperated string of a single column from a select statement

Parameters:@SQLstring = The complete SELECT statement used to get the column rows
@ColumnName = The name of the column to use (this would also be in @SQLstring)
@list = the returned string of comma seperated values
Example: 
--Get CSV list of warehouses 
USE PUBS
declare @SQLstring nvarchar(4000), @ColumnName varchar(25), @list varchar(8000)
SET @SQLstring = 'SELECT * FROM employee'


SET @ColumnName = 'emp_id'

EXEC ap_ReturnCSV @SQLstring, @ColumnName, @list OUTPUT

print @list


REVISION:   REVISIONDATE    ACTION
--------    ------------        ------
**********************************************************************************************************/

AS  


BEGIN 
   DECLARE @newSQL nvarchar(4000)


   SET @newSQL = 'SELECT DISTINCT CONVERT(varchar(25),' + @ColumnName + ') ' +
RIGHT(@SQLstring,(LEN(@SQLstring)-CHARINDEX ('FROM' , @SQLstring))+1)


--convert UIDs to string of comma seperated values

CREATE TABLE #workingtable 
(
i int IDENTITY (1, 1) NOT NULL ,
vc varchar (10),
) 

insert into #workingtable (vc) EXECUTE sp_executesql @newSQL


declare
    @lasti int

select
    @list = '',
    @lasti = 1
update
    #workingtable
set @list = 
   case
                       when @lasti = i then vc
                       else @list + ',' + vc
                   end,
         @lasti = i

   

END

Read 1,256 times
(2 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating