SQL Server Performance

How do I aggregate strings?

Discussion in 'General Developer Questions' started by kkt, Nov 1, 2006.

  1. kkt New Member

    Hi all,

    has anyone ever come to a solution in joining strings together in a comma separated fashion? Something like an aggregate e.g. SELECT X, JOIN(myStringColumn), FROM Y GROUP BY X? And all that in SQL Server 2000 (I know you can write your aggegates in SQL 2005)? It is a bit tricky to acheive something like this? Any ideas?

    Good morning everyone

    KKT
  2. Roji. P. Thomas New Member

  3. kkt New Member

    What would the implementation look like? Something like a loop in all rows of the same group? I cannot imagine how to imlement something like that. Any ideas?

    KKT
  4. ramkumar.mu New Member

    Try whether this will help...

    CREATE FUNCTION dbo.ConcatBooks(PublisherName varchar(30))
    RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @Output varchar(8000)
    SET @Output = ''

    SELECT @Output =CASE @Output
    WHEN '' THEN au_lname
    ELSE @Output + ', ' + au_lname
    END
    FROM Publisher
    WHERE PublisherName = @PublisherName

    RETURN @Output
    END
    GO

    SELECT DISTINCT State, dbo.ConcatBooks(PublisherName)
    FROM Authors
    ORDER BY State


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. Adriaan New Member

    You can concatenate with a SELECT query in a UDF, which will be faster than a cursor-based approach - in most cases.

    Beware that such a UDF will always slow down the query - perhaps not noticeable on small resultsets, but might be a problem on larger resultsets.

    In SQL 2000, the concatenated list is limited to VARCHAR(8000) or NVARCHAR(4000). I presume SQL 2005 can use VARCHAR(MAX) or NVARCHAR(MAX).


    CREATE FUNCTION dbo.ConcatString (@lookupkey INT)
    RETURNS VARCHAR(8000)
    AS

    DECLARE @Return VARCHAR(8000)
    SET @Return = ''

    SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN '' ELSE ',' END + column
    FROM table
    WHERE key = @lookupkey
    ORDER BY column

    RETURN (@Return)
    GO

  6. kkt New Member

    thanks guys. Clever solution. I only have small tables to work with so performance should not be a problem

    KKT
  7. Roji. P. Thomas New Member

  8. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan
    SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN '' ELSE ',' END + column
    FROM table
    WHERE key = @lookupkey
    ORDER BY column
    And again, if you let me, the ORDER is not guaranteed.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  9. ramkumar.mu New Member

    The world has shrunk - Anonymous<br />So will the query when SSP gurus touch it - Ram [<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  10. Adriaan New Member

    What was it Roji - the ORDER BY is not guaranteed because the query is also doing the concatenation? or it that the ORDER BY probably works, although it shouldn't?
  11. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    What was it Roji - the ORDER BY is not guaranteed because the query is also doing the concatenation? or it that the ORDER BY probably works, although it shouldn't?
    It is that the ORDER BY probably works, although it shouldn't.

    Here we are using ORDER BY to specify the internal execution order, But ORDER BY only guarantees the order of the end result.

    While we are at it, the concatenation technique itself, is undocumented and NOT foolproof and there are numerous cases where it will fail.




    Roji. P. Thomas
    http://toponewithties.blogspot.com
  12. Madhivanan Moderator

    quote:Originally posted by kkt

    Hi all,

    has anyone ever come to a solution in joining strings together in a comma separated fashion? Something like an aggregate e.g. SELECT X, JOIN(myStringColumn), FROM Y GROUP BY X? And all that in SQL Server 2000 (I know you can write your aggegates in SQL 2005)? It is a bit tricky to acheive something like this? Any ideas?

    Good morning everyone

    KKT
    Where do you want to show data?
    If you use front end application, do concatenation there

    Otherwise read this why Function approach is preferred
    http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true


    Madhivanan

    Failing to plan is Planning to fail
  13. Roji. P. Thomas New Member

  14. FrankKalis Moderator

    After all, we're talking about a presentational issue here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. Adriaan New Member

    Yes Frank, but say you have a number of rows (P) from the parent table, and a number of rows (P*C) from the child table from which the column values must be concatenated.

    When the client app does the concatenation, you have to return (P*C) rows.
    When SQL Server does the concatenation, you have to return (P) rows.

    There might well be a break-off point where SQL Server would spend more time returning (P*C) values rows than concatenating and returning (P) rows.
  16. FrankKalis Moderator

    Yes, there is always a trade-off to be considered.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  17. Roji. P. Thomas New Member

    Unfortunately, there are valid scenarios where the string concatenation should be performed at the server side itself. For eg, a batch job that aggregates data, which obviously has no front end.

    The only supported method to achieve the desired results is using a cursor or a loop.

    An approach using the T-SQL update extension is anyways preferred over the approach discussed here.
    Like

    UPDATE Table
    SET @string = @String + col

    Note that the table is not actually getiing updated there.



    Roji. P. Thomas
    http://toponewithties.blogspot.com
  18. FrankKalis Moderator

    Speaking of the order of execution. This doesn't seems to be defined in the UPDATE method either, or?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  19. Roji. P. Thomas New Member

    Well, No Order guarantees and even that approach is not documented.

    But the

    UPDATE table
    SET @var = col = @var

    extension is documented.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  20. Madhivanan Moderator

    quote:Originally posted by Adriaan

    Yes Frank, but say you have a number of rows (P) from the parent table, and a number of rows (P*C) from the child table from which the column values must be concatenated.

    When the client app does the concatenation, you have to return (P*C) rows.
    When SQL Server does the concatenation, you have to return (P) rows.

    There might well be a break-off point where SQL Server would spend more time returning (P*C) values rows than concatenating and returning (P) rows.
    It depends on number of rows. If there are hundreds of thousands of rows, and if you use function in sql to do concatenation, doesnt it take too much time?

    Madhivanan

    Failing to plan is Planning to fail
  21. Mateusz New Member

    You can find nice solutions here:
    http://biblog.pl/?p=140
  22. Madhivanan Moderator

    [quote user="Mateusz"]
    You can find nice solutions here:
    http://biblog.pl/?p=140
    [/quote]
    Note that the question was posted in versions 7 and 2000 forum in which your methd wont work

Share This Page