How do you group rows

Barry1337 · #1 (**permalink**) 10-13-11, 05:36

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.

shureg · #2 (**permalink**) 10-13-11, 07:35

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?

r937 · #3 (**permalink**) 10-13-11, 07:38

Quote:

Originally Posted by shureg

in mysql GROUP_CONCAT function?

yes

but i don't think there is anything in ANSI SQL (this forum)

stolze · #4 (**permalink**) 10-14-11, 07:09

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.

tonkuma · #5 (**permalink**) 02-06-12, 05:10

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