-1

I am stuck in situation where I need a comma separated list from group by result sets. Let me give you a typical schema : 

        username | lotno | img
        ------------------------
        a        | 0001  | 1.jpg
        ------------------------
        a        | 0001  | 2.jpg
        ------------------------
        a        | 0002  | 1.jpg
        ------------------------
        a        | 0002  | 2.jpg
        ------------------------
        a        | 0002  | 3.jpg
        ------------------------
        b        | 0003  | 1.jpg
        ------------------------

Now I want a group by result set like this :

    <pre>

    username | lotno       | count
    ------------------------------
    a        | 0001, 0002  | 5
    ------------------------------
    a        | 0001        | 1
    ------------------------------

    </pre>

How can I achieve this without writing a function or programming ? Can I do this using a query in Oracle. Do not mind, the table structure shown is drawn using tag. Thanks in advance.

Samcoder
  • 345
  • 1
  • 4
  • 14
  • What version of Oracle are you using? What does "without programming" mean to you? – Justin Cave May 12 '14 at 17:07
  • without programming means, I could use Programming language like Java or others which will use the simple group by result set and output into what I required. – Samcoder May 12 '14 at 17:16

1 Answers1

1

Is the count field the number of lines per username and lotno (this is not clear from your example) ? If so, this could be:

select username, listagg(lotno, ', ') within group (order by lotno), nb
from
(
  select username, lotno, count(*) nb
  from your_table
  group by username, lotno
)
group by username, nb;

You can use the listagg clause from 11gR2.

Emmanuel
  • 13,935
  • 12
  • 50
  • 72