Re: Vertical result with Oracle SQL?

On Sep 12, 5:17 pm, georgino...._at_gmail.com wrote:
> On Sep 11, 6:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > georgino...._at_gmail.com wrote:
> > > Hi guys,
> > > Here is what I need to get.
>
> > > Columns: Id, Name, Program, cost
> > > Data sample:
> > > 1, George, Program 1, $10
> > > 1, George, Program 2, $10
> > > 1, Peter, Program 1, $10
>
> > > The result line should looks like this:
> > > 1; George, Peter; Program 1, Program 2, $30
>
> > > Basically I need to concatenate Names and Programs and sum the cost
> > > for each ID
>
> > > I am stuck here; any idea would be greatly appreciated
>
> > > Thanks
> > > Georgino
>
> > What you need to a cross tabulation.
>
> > In any version of Oracle this can be done with DECODE. Since 9i
> > with CASE and in 11g with PIVOT.
>
> > Demos in Morgan's Library atwww.psoug.org.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> > - Show quoted text -
>
> Sorry but I don't see how you can use decode or case in this
> situation.
> I am trying to generate one vertical line from 3 horizontal where the
> name and program fields will be concatenated into one.
> With decode or case I will still get 3 rows.

Is this a string aggregation question?
http://www.williamrobertson.net/documents/one_row.html

btw it looks like you want to combine vertical values into a horizontal one (column into row), rather than the other way around. Received on Wed Sep 12 2007 - 17:28:20 CDT