ListAgg Function in 11gR2

This function has been around for a while in some other databases. It's mainly for display or reporting purposes - changing a long one dimensional list into a short two dimensional list.

Think of a card game where 13 cards are dealt to 4 players. The cards dealt to a player might look like this

Suit	Card
Heart	King
Heart	Jack
Heart	3
Spade	Ace
Spade	10
Spade	7
Spade	3
Spade	2
Diamond	Ace
Diamond	10
Diamond	8
Diamond	7
Diamond	6

But what you really want to show is this:

Suit	Cards
Heart	King, Jack, 3
Spade	Ace, 10, 7, 3, 2
Diamonds	Ace, 10, 8, 7, 6

now you can with ListAgg

The TrainTrack notation looks like:

Notice in the diagram that there is an optional OVER clause. That should be a hint that this can also be used as an analytic function. So what does that mean to you?

In our card game example, I showed only one player's cards. More likely the table would have a column for player as well, as so.

Player	Suit	Card

What I need to look at is the suit break down by player. I want the suits to repeat for each player. Using the OVER clause I can accomplish that.