Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: How do you group rows

    Hello,

    I have the following database:

    1 | 'a'
    1 | 'b'
    1 | 'c'

    And I want the following (for the sake of frequent itemset mining) :

    1 | 'a','b','c'

    Is there an operator in SQL to perform this action? I know you can only use aggregate functions, but I don't understand why.

  2. #2
    Join Date
    Oct 2011
    Location
    Hamburg, Germany
    Posts
    18
    in oracle you can use listagg function
    Code:
    SELECT id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS n
    FROM   tab
    GROUP BY id;
    in mysql GROUP_CONCAT function?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shureg View Post
    in mysql GROUP_CONCAT function?
    yes

    but i don't think there is anything in ANSI SQL (this forum)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In ANSI/ISO SQL, you'd have to use a recursive query that does the string concatenation. Also, I'm not sure if XMLAGG() is a standardized function. If so, it would be another option. by constructing an XML document, doing the aggregation there and then just getting the text data.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ANSI SQL:2003 includes "Part 14: SQL/XML".

    See general descriptions in
    Overview of SQL:2003
    page 65-67: Part 14: SQL/XML
    page 93-95: XMLAGG

    SQL:2003 Has Been Published

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Tonkuma, do you know if LISTAGG is in SQL2003 or SQL2007?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I searched word "LISTAGG" in draft PDFs of SQL2003 and not found the word.

    I have no material of SQL2007, now.

  8. #8
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    And no LISTAGG found neither in SQL-2011 nor SQL-2015 draft.

Posting Permissions

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