Re: Vertical result with Oracle SQL?

From: <georgino.no1_at_gmail.com>
Date: Fri, 14 Sep 2007 13:19:05 -0700
Message-ID: <1189801145.253985.172380@w3g2000hsg.googlegroups.com>

On Sep 12, 6:28 pm, William Robertson <williamr2..._at_googlemail.com> wrote:
> 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-Hidequoted 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.- Hide quoted text -
>
> - Show quoted text -

Hi guys,
Yes I want vertical to horizontal.
Here is the solution from one friend of mine. Just in case someone would like to do this in the future.

Creating sample data

---

create table george(app_id number, contact varchar2(16), children varchar2(16), program varchar2(16), cost number) /
insert into george values(1, 'George', 'Patrik', 'Swimming', 50) /
insert into george values(1, 'George', 'Nicole', 'Archery', 45) /
insert into george values(1, 'George', 'Patrik', 'Badminton', 10) /
insert into george values(1, 'George', 'Suzy', 'Swimming', 5) /
insert into george values(1, 'Glenn', 'Joe', 'Swimming', 50) /
insert into george values(1, 'Glenn', 'Nicole', 'Archery', 45) /
insert into george values(1, 'Glenn', 'Joe', 'Badminton', 10) /
insert into george values(1, 'Glenn', 'Suzy', 'Swimming', 5) /
insert into george values(1, 'Glenn', 'Jenny', 'Rowing', 100) /
commit
/

The Query

---

WITH key AS (
  SELECT app_id, contact, sum(cost) total     FROM george
    GROUP BY app_id, contact
),

children AS (
select app_id, contact, ltrim(sys_connect_by_path(children, ','),',') children_list
  from ( select app_id, contact, children

               ,row_number() over (partition by app_id, contact order by
children) rn

               ,count(*) over (partition by app_id, contact) cnt
           from ( SELECT app_id, contact, children
                    FROM george
                    GROUP BY app_id, contact, children )
        )

  where rn = cnt
  start with rn = 1
  connect by prior rn = rn-1 and prior app_id = app_id and prior contact
= contact
),

program AS (
select app_id, contact, ltrim(sys_connect_by_path(program, ','),',') program_list
  from ( select app_id, contact, program

               ,row_number() over (partition by app_id, contact order by
program) rn

               ,count(*) over (partition by app_id, contact) cnt
           from ( SELECT app_id, contact, program
                    FROM george
                    GROUP BY app_id, contact, program )
        )

  where rn = cnt
  start with rn = 1
  connect by prior rn = rn-1 and prior app_id = app_id and prior contact
= contact
)

SELECT k.app_id, k.contact, c.children_list, p.program_list, k.total   FROM key k

      ,children c
      ,program p
  WHERE c.app_id = k.app_id
    AND c.contact = k.contact

    AND p.app_id = k.app_id
    AND p.contact = k.contact

The Result

---

1 George Nicole,Patrik,Suzy
Archery,Badminton,Swimming
110

1	 Glenn	 Jenny,Joe,Nicole,Suzy
Archery,Badminton,Rowing,Swimming	  210

Received on Fri Sep 14 2007 - 15:19:05 CDT