sqlsybase

SQL string aggregation based on a group by

I have a dataset of INT, varchar. The ints can be grouped into small sets of 1 to 5, and I want to see the count of all the corresponding texts. E.g. input table (apols for format)

 1,a
 2,a
 2,b
 3,a
 3,q 
 3,z
 4,a

I can group by the Int, but have no idea how I can concat across rows. In the above i would want to know counts of:

a,2
ab,1
aqz,1

Can I do this purely in SQL, ideally in a generic db agnostic way?

Solution

  • Sybase supports the LIST() function. So:

    select chrs, count(*) as cnt
from (select t.number, list(t.chr, '' order by t.chr) as chrs
      from t
      group by t.number
     ) n
group by chrs
order by count(*) desc;