Aggregate function for text strings?

quote:Originally posted by lars_gustafsson


Dear wizards of SQL,

I am working on a case where I am in dire need of a function which concatenates strings in the same way that SUM() works for numbers - an aggregate function for string concatenation. I have not found anything in the SQL Server documentation and only very little information on the web.

Does anyone know of anything regarding this matter? Any help would be greatly appreciated.

Best regards
Lars

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8877


hsGoswami
ghemant@gmail.com

I'm not sure, if this is what you are looking for, but there is a technique called aggregate concatenation in SQL Server. This is no build-in single function like SUM(), but rather something like this<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE note<br />(<br />col1 VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />)<br />INSERT INTO note (col1) VALUES('Notes1')<br />INSERT INTO note (col1) VALUES('Notes2')<br />INSERT INTO note (col1) VALUES('Notes3')<br />INSERT INTO note (col1) VALUES('Notes4')<br />INSERT INTO note (col1) VALUES('Notes5')<br />INSERT INTO note (col1) VALUES('Notes6')<br />INSERT INTO note (col1) VALUES('Notes7')<br />INSERT INTO note (col1) VALUES('Notes8')<br />INSERT INTO note (col1) VALUES('Notes9')<br />INSERT INTO note (col1) VALUES('Notes10')<br /><br />DECLARE @allnotes VARCHAR(8000)<br />SELECT <br />@allnotes = ISNULL( @allnotes + ', ', '' ) + col1 <br />FROM <br />note<br />SELECT <br />@allnotes<br />DROP TABLE note<br /></font id="code"></pre id="code"><br /><br />HTH<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Please start a new thread on this. This will improve your chances to get a quick and good solution. And while you're at it, please provide what your data looks like and what your result should look like.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

quote:Originally posted by anju

I got what u said
but in my project i need to concatenate the strings along with group by funtion

for example
select Arefno, Sum(ProjectID) from Tbl group by Arefno;
i need concatenation of strings instead of sum function
is it possible.

Refer this
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8971


Madhivanan

Failing to plan is Planning to fail