georgino.no1_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.
Then UNION all together three queries ... one for col1, one for col2
and one for col3. Be sure to alias the first column name to the final
column name you want to see.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Sep 12 2007 - 16:51:38 CDT
