Merging Resulting Records into single field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merging Resulting Records into single field?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-08-12 : 18:42:56

I know the "MERGE" function below doesn't exist but is there someway to accomplish this without a stored proc?

select MERGE(CourseTitle,"|") from Courses where Certificate = 1 Group by Certificate

I want one record returned that looks like this...

Course1|Course2|Course3

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-08-13 : 00:15:04

Ok. I went ahead and did a stored procedure. I would like for the table name, where clause, and select field to be variables passed in but I can't get it to work like that. Is it possible? Here is my code so far...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE sp_Merge 
	@searchFor varchar(100)
AS
	
BEGIN

DECLARE @MergedItem varchar(1000)
DECLARE @TempString varchar(100)
	
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT FirstName
FROM Person.Person where LastName = @searchFor

OPEN c1

FETCH NEXT FROM c1
INTO @MergedItem

WHILE @@FETCH_STATUS = 0
BEGIN

	FETCH NEXT FROM c1
	INTO @TempString
	
	SET @MergedItem = @MergedItem + '|' + @TempString
	
END

SELECT @MergedItem

CLOSE c1
DEALLOCATE c1
	
END
GO

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 00:43:49

there is no merge function in the MSSQL server
r u using MS SQL server
if u want in sql server try to use funciton or XML PATH() see in books online for details

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources