#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9

    Aggregate Function to Concatenate


    Hi All I have this query that pulls a bunch of data and I group it by X I want to get the Concatenated string of all the others rather than max or min. IE with max it looks like this:

    SELECT MAX(container), groupid FROM mytable GROUP BY groupid

    This returns data:
    "c" "G1"
    "c" "G2"

    But rather than the max container I want a concat sring with a seperator of ", " in between. Is this possible?

    I want it to return:
    "a, b, c" "G1"
    "a, b, c" "G2"

    Thanks!
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Give some sample raw data.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by Vomster
    Give some sample raw data.
    Code:
    CREATE TABLE mytable
    ( container  VARCHAR(9)
    , groupid VARCHAR(9) );
    INSERT INTO mytable VALUES ( 'tom', 'G1' );
    INSERT INTO mytable VALUES ( 'duck', 'G1' );
    INSERT INTO mytable VALUES ( 'hairy', 'G1' );
    INSERT INTO mytable VALUES ( 'curly', 'G2' );
    INSERT INTO mytable VALUES ( 'larry', 'G2' );
    INSERT INTO mytable VALUES ( 'moe', 'G2' );
    INSERT INTO mytable VALUES ( 'humpty', 'G3' );
    INSERT INTO mytable VALUES ( 'dumpty', 'G3' );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    9
    Yes sir thanks R397 for that raw data. Sorry for the late reply I was taking some time off the computer due to a shouler injury.

    love the sample data r397!! haha

    So the sample data like r397 post above is:
    Code:
    tom	G1
    duck	G1
    hairy	G1
    curly	G2
    larry	G2
    moe	G2
    humpty	G3
    dumpty	G3
    So when I run the query I want to run:
    Code:
    SELECT CONCAT(container, ', '), groupid FROM mytable GROUP BY groupid
    and I want it to return:
    Code:
    tom, duck, hairy	G1
    curly, larry, moe	G2
    humpty, dumpty		G3

    Please help

IMN logo majestic logo threadwatch logo seochat tools logo