Join Column Values

,

This script will join a single selected column from a select statement into a varchar variable. This works similarly to the javascript join function with a few additional features. By default the values will be joined with commas unless an alternate delimiter is supplied. The user can specify if the delimited values should quoted.

To use this sp only the selected column, the from clause and the where clause are supplied rather than the entire select statement.

--------------------------------------------------------------------------------------
IF EXISTS (select * from sysobjects where id = object_id('usp_Join') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.usp_Join
GO

CREATE  PROCEDURE dbo.usp_Join( @vcColumnName 	varchar(100),
				@vcFrom		varchar(1000),
				@vcWhere	varchar(2000),
				@bQuoteValues	bit,
				@vcJoinString   varchar(8000) OUTPUT,
				@vcDelimiter	varchar(20) = ','
				
				
)
/**************************************************************************
DESCRIPTION:  Concatonates table column into a delimited string

PARAMETERS:
	@vcColumnName	- the column to be joined
	@vcFrom		- table from where data will be extracted
	@vcWhere	- filter conditions
	@bQuoteValues	- 1 = quote values, 0 = no quotes
	@vcJoinString	- Concatonated output of procedure
	@vcDelimeter	- Optional. Character(s) that will separate column values. 			
	

USAGE:	  
	DECLARE   @vcJoinStr		varchar(8000)
     	EXECUTE dbo.usp_Join 'au_lname', 
		'pubs.dbo.authors',
		'au_lname like ''d%''', 
		1, 
		@vcJoinStr OUTPUT,
		'|---|'
	SELECT @vcJoinStr
					

AUTHOR:	Karen Gayda

DATE: 	10/30/2003

MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	------------------------------------

***************************************************************************/

AS
BEGIN
	DECLARE	@nvcSQL			nvarchar(4000),
		@nvcParmDef		nvarchar(500)
	
	
	SET @nvcSQL = N'SET @vcJoinStringOUT='''' SELECT @vcJoinStringOUT = @vcJoinStringOUT + ' +
		      'CASE WHEN ' + CAST(@bQuoteValues as varchar) + ' = 1 
		      THEN ''' + @vcDelimiter + '''+CHAR(39)+CAST(' + @vcColumnName + ' as varchar)+CHAR(39)' +
						' ELSE ''' + @vcDelimiter + '''+CAST(' + @vcColumnName + ' as varchar)' + 
						' END ' +
			'FROM (SELECT DISTINCT ' +  @vcColumnName + ' FROM ' + @vcFrom +
			' WHERE ' + @vcWhere + ') t'
	
	SET @nvcParmDef = N'@vcJoinStringOUT varchar(8000) OUTPUT'
	EXECUTE sp_executesql @nvcSQL, @nvcParmDef, @vcJoinStringOUT=@vcJoinString OUTPUT

	SET  @vcJoinString = RIGHT( @vcJoinString,LEN( @vcJoinString) - Len(@vcDelimiter))
        RETURN(0)

END

Rate

5 (1)

Share

Share

Rate

5 (1)