SQL Server Performance

Aggregate function for text strings?

Discussion in 'General Developer Questions' started by lars_gustafsson, Jun 21, 2005.

  1. lars_gustafsson New Member


    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
  2. ghemant Moderator

    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
  3. FrankKalis Moderator

    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 />
  4. lars_gustafsson New Member

    <br /><br />Thank you all for the speedy response! I found exactly what I needed! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />// Lars
  5. anju New Member

    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.


  6. FrankKalis Moderator

    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)
  7. Madhivanan Moderator

    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

Share This Page