Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Aggregating functions for strings in a group by

    If I have a table:

    TAB1

    Code:
    PKey          Col1StrName          Col2IntDebt          Col3StrItem
    1               Michael                 2.00                    Toothpaste
    2               Josh                     3.00                    Shampoo
    3               Karen                   1.00                     Softdrink
    4               Josh                     2.50                    Soap
    5               Michael                 5.75                    Lollies
    6               Michael                 0.25                    Pen
    7               Karen                   3.25                     Milk
    If I do a group by I can aggregate the debts to:


    Code:
    Name       Debt   
    Michael    8.00
    Josh        5.50
    Karen      4.25
    With the following code:


    Code:
    select  
       Col1StrName as Name, SUM(Col2IntDebt) as Debt
    from  
       TAB1 
    group by  
       Col1StrName

    I would like to include a column such that:


    Code:
    Name       Debt     Items
    Michael    8.00      Toothpaste, Lollies, Pen
    Josh        5.50      Shampoo, Soap
    Karen      4.25       Softdrink, Milk

    What is the aggregate function to use for the string?

    TIA!
    Last edited by g11DB; 09-14-08 at 22:03.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll need to write your own function. Here is the technique:
    http://sqlblindman.googlepages.com/c...limitedstrings
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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