SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using COALESCE to Build Comma-Delimited String
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sqlwayne
Starting Member

USA
1 Posts

Posted - 10/02/2007 :  19:21:58  Show Profile  Visit sqlwayne's Homepage  Reply with Quote
Very cool code, just what I was looking for! I pulled it into an ASP page for reporting election results.
Go to Top of Page

bitburner
Starting Member

1 Posts

Posted - 05/13/2008 :  16:33:01  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

If "everything is back-end", why do you need to concatenate the personnelnumbers at all?



E 12°55'05.25"
N 56°04'39.16"



OP wants to see the result in Query Analyser

Madhivanan

Failing to plan is Planning to fail



Well the problem in your case is the number of string concats that take place when doing this. So the solution is to reduce the number of concatenation operations by using a temp table.

I know this is a big ugly monster and normally I wouldn't recommend it to anyone - but for what you need (just seeing the stuff in QA) this will work a lot faster; I was able to run it in about 6 1/2 minutes versus your 20+ doing it without the cursor and temp table on a Core 2 Duo Laptop with 2 GB of RAM).

CREATE TABLE #TempColTable (ConVal nvarchar(max))

DECLARE collation_csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT
[Value]
FROM
TestTable

OPEN collation_csr

DECLARE @insertSection as nvarchar(max)
DECLARE @numberPlaceHolder as nvarchar(10)
DECLARE @rowCounter as bigint;

FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;

SET @insertSection = @numberPlaceHolder + ', ';
SET @rowCounter = 0;

WHILE(@@FETCH_STATUS = 0)
BEGIN

FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;

SET @insertSection = @insertSection + @numberPlaceHolder + ', ';

IF(@rowCounter = 7500)
BEGIN
INSERT INTO #TempColTable (ConVal) VALUES (@insertSection);
SET @insertSection = '';
SET @rowCounter = 0; --reset the counter so we can insert this batch and move on
END
ELSE
BEGIN
SET @rowCounter = @rowCounter + 1;
END
END

CLOSE collation_csr
DEALLOCATE collation_csr

DECLARE @insertedRecords as bigint;
DECLARE @number varchar(max)

SELECT @insertedRecords = COUNT(ConVal) FROM #TempColTable

IF(@insertedRecords = 0)
BEGIN
SET @number = @insertSection;
END
ELSE
BEGIN
SELECT
@number = COALESCE(@number + ', ', '') + [ConVal]
FROM
#TempColTable
END

DROP TABLE #TempColTable

SELECT @number;

SELECT LEN(@number);
Go to Top of Page

rgerald
Starting Member

USA
2 Posts

Posted - 09/10/2008 :  17:11:18  Show Profile  Reply with Quote
CAVEAT: This technique fails for me when the total number of characters in the variable is more than 255 (even when the variable is declared as varchar(8000)).

The final SELECT statement only shows the first 255 characters. I am using SQL Server (2000). Is that an issue?

Rand
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/10/2008 :  17:24:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You only get to SEE the first 255 characters in Query Analyzer, because that is the default setting.
You can change this number of characters in Tools -> Option menu.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

rgerald
Starting Member

USA
2 Posts

Posted - 09/10/2008 :  17:31:02  Show Profile  Reply with Quote
One more bit:

The problem I just described only applies to "output to text" or "output to file" - those cut off at 255 characters.

If you use "output to grid", the full 8000 characters will be there for a varchar(8000) variable.

Rand
Go to Top of Page

Piyush
Starting Member

India
8 Posts

Posted - 07/25/2011 :  02:17:07  Show Profile  Visit Piyush's Homepage  Reply with Quote
Its a nice article.
If you want to see further more, then just follow the link:
Working with NULLS in SQL Server - PART 2
unspammed

Piyush Bajaj
Go to Top of Page

Kinnerton
Starting Member

21 Posts

Posted - 12/07/2012 :  10:53:06  Show Profile  Reply with Quote
I know it is prehistoric thread - but you don't need to use variables or coalesce. You can do it all inline. See below.

declare @table table (
	result_number int
)

insert into @table
	select 12345 union
	select 23456 union
	select 34567 union
	select 45678 union
	select 56789 union
	select 67890
	
select stuff(convert(varchar(max),(
			select ','+ cast(result_number as nvarchar(255)) 
			from @table b
			FOR XML PATH('')
		)),1,1,'')

Edited by - Kinnerton on 12/07/2012 10:55:01
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000