Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Unanswered: Varchar aggregation

    Hi all,
    Do you know any function or trick to make a string field concatenation (without a very-very-very-slow while/if-goto loop)?
    e.g.: something like sum(<varchar field>) with a group by
    (i'll be happier if it works with sql7).
    César.

  2. #2
    Join Date
    Dec 2003
    Location
    Pakistan
    Posts
    2

    Re: Varchar aggregation

    u can simply concat the varchar fields by placing + sign between the field names which u want to cancat.
    i.e
    select c1+c2+c3 from tblName....

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think s/he is looking for

    Code:
    USE Northwind
    GO
    DECLARE @Names varchar(8000)
        SET @Names = ''
     SELECT @Names = @Names + ',' + LastName  
       FROM Employees
     SELECT SUBSTRING(@Names,2,LEN(@Names)-1)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Almost perfect!

    Thank's Brett,
    I'm wondering why SQL designers allow MAX(varchar) or MIN or COUNT b.u.t. SUM().
    Aren't they supposed to work with data? (mainly "string" data!?)
    There are a lot of situations where I need to create a variable-size string based in queries & subqueries...
    You actually solve the main case, I think I must forget optimizations and create temporary tables to work with variables.
    My worst case scenario is a cursor-like lookup, when sql7 scalar variables are not able to concatenate subqueried values without a very slow loop.
    Do you know where can I send a "suggestion" to MS Sql designers?
    César

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Think about it...what does sum do?

    What should SUM(A+B+C) give you?

    String parsing is different than handling numerics....

    Frankly, I was amazed when I found out the syntax to put the strings together...

    Made (makes) no sense to me...but happens to be very usefule (sometimes)

    Contact M$...Evil Empire, Redmon, Wash
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •