Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Dayton, OH
    Posts
    8

    Unanswered: Aggregate String Concatenation function?

    Hi,

    I'm trying to do the following, but am getting errors because (obviously) SUM doesn't work with data types of nvarchar.

    SELECT
    SUM(CASE WHEN FieldName = 'SPECIFIC' THEN Tolerance ELSE '' END) AS 'Specific Tolerance'
    FROM FIELD_TOLERANCE
    GROUP BY Area

    Tolerance holds values such as '100 +/- 25'. Obviously the first thought would be to seperate the two parts '100' and '25' into seperate fields and then have the program reconstruct it. Unfortunatly sometimes the value is odd, such as '100 +10 -25' (meaning a range of 75 - 110 with a target of 100).

    Is there any way to put effectively sum up the Tolerance. Also, I know for a fact the FieldName 'SPECIFIC' will only be in the database once for each area.

    Thanks,
    Ryan

  2. #2
    Join Date
    Feb 2003
    Location
    Dayton, OH
    Posts
    8
    I searched Books Online for aggregate functions as well as just functions. I found nothing listed under either which would help.

    Should I create a function for this task? How would I create a function to do this?

    Thanks,
    Ryan

  3. #3
    Join Date
    Feb 2003
    Location
    Dayton, OH
    Posts
    8
    We no longer need to do what I was asking about. But is there a way? I'm curious.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?

  5. #5
    Join Date
    Feb 2003
    Location
    Dayton, OH
    Posts
    8
    Originally posted by MCrowley
    If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?
    well I have a table like so (dashes inserted for web formating purposes):

    Area------Field-----Tolerance
    1----------FieldA---100 +/- 12
    1----------FieldB---100 +/- 13
    2----------FieldA---97 +3 -7
    2----------FieldC---95 +/- 5

    Area type = int
    Field type = varchar
    Tolerance type = varchar

    I want the results I want are as follows (dashes inserted for web formating purposes):

    Area------FieldATolerance---FieldBTolerance----FieldCTolerance
    1----------100 +/- 12---------100 +/-13
    2----------97 +3 -7---------------------------------95 +/- 5

    This way I can pass the values for field I want the tolerances for and get them all back in one record. This allows the Tolerance table to hold tolerances for different fields, yet make retrieving the tolerances easy.

    -Ryan

    ps. Thanks for the reply
    Last edited by riddelrp; 09-10-03 at 14:40.

Posting Permissions

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