Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: How to aggregate the same data from one column

    I have loaded .csv file into a table with three columns. In one of the columns are repeated exactly the same data:

    ID CN OPIS
    -------------
    1 123 AAA
    2 000 eee
    3 123 BBB
    4 555 ccc

    Is it possible to combine items 1 and 3 so as to have a one record of the aggregate of:

    ID CN OPIS
    -------------
    1 123 AAA,BBB
    2 000 eee

    4 555 ccc

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's possible
    Code:
    SELECT MIN(id)
         , cn
         , GROUP_CONCAT(opis)
      FROM daTable
    GROUP
        BY cn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    yes, it's possible
    Code:
    SELECT MIN(id)
         , cn
         , GROUP_CONCAT(opis)
      FROM daTable
    GROUP
        BY cn
    Thanks.
    I wanted to use this 'group' at the time of loading the file to the table, but this proved impossible. Is after reading the data to the table can be grouped yet or should do it before? I would like to have tables with records already grouped.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    I would like to have tables with records already grouped.
    Code:
    CREATE TABLE already_grouped
    ( id INTEGER NOT NULL
    , cn INTEGER NOT NULL
    , opis VARCHAR(37) NOT NULL )
    SELECT MIN(id)
         , cn
         , GROUP_CONCAT(opis)
      FROM daTable
    GROUP
        BY cn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I have no idea what your columns represent in terms of business roles but I'd be very very wary to merge data in that sort f manner, especially as R(37 has provided a simple enough way to get to where you want.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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