Re: dynamic table creation

From: Guido Konsolke <Guido.Konsolke_at_triaton.com>
Date: Mon, 4 Aug 2003 11:12:58 +0200
Message-ID: <1059988337.934498_at_news.thyssen.com>

Hi Krzys,

the difficult part is that you have to know how many different act_base_id's (and which!) exist. If you can sort that out, it's easy. You can run a query directly from excel or spool the output of the query to ...csv format. Read upon SPOOL command in the SQL/PLUS docs. To seperate the columns you have to use
>SET COLSEP ,<. This will get you the comma
separated file you need.

About the query:
my solution is simplified (I didn't want to create your 3 tables).

SELECT sales_id,

SUM(CASE WHEN act_base_id=100 THEN amt ELSE 0 END) amt100,
SUM(CASE WHEN act_base_id=120 THEN amt ELSE 0 END) amt120,
SUM(CASE WHEN act_base_id=130 THEN amt ELSE 0 END) amt130,
SUM(CASE WHEN act_base_id=150 THEN amt ELSE 0 END) amt150,
SUM(CASE WHEN act_base_id=170 THEN amt ELSE 0 END) amt170,
SUM(CASE WHEN act_base_id=200 THEN amt ELSE 0 END) amt200

FROM (insert your query here)
GROUP BY sales_id;

Note that I used a column alias on count(*).

I'm pretty sure that someone can find a better solution.

Greetings & hth,
Guido

"Krzys!" <krzys78_at_polbox.com> schrieb im Newsbeitrag news:bgdejv$1rd4$1_at_foka.acn.pl...
> WOW .. great !
>
> version of ORACLE 8.1.6
>
>
> Let me describe whole situation. I need to create a raport I could
> export and analyze in Excel.
> Here is a query I have to "publish" in excel
>
> query:
> select cf.sales_id, af.ACT_BASE_ID, count(*)
> from calls_fact cf, act_fact af, act_base_name abn
> where cf.ACT_FACT_ID=af.ACT_FACT_ID and
 af.ACT_BASE_ID=abn.ACT_BASE_ID
> and abn.lang_id='POL'
> group by cf.sales_id, af.ACT_BASE_ID;
>
> result:
> sales_id act_base_id count(*)
> 1 100 20
> 1 120 30
> 1 170 5
> 2 130 10
> 2 170 90
> 3 100 5
> 4 150 20
> 4 200 4
> 5 120 40
> ETC. ETC.
>
> so every sales (sales_id) has a few different activities
 (act_base_id)
> and he make this activities a lot of times every day. I hope it's
 easy
> and clear. But I need export this result to excel to a bit
 different
> format. I could say format which my manager is able to understand ;)
> Each line should contain one sales like that:
>
>
> sales_id 100 120 130 150 170 200
> 1 20 30 5
> 2 10 90
> 3 5
> 4 20 4
> 5 40
>
>
> is it clear ? I'm sure yes
>
> So I need to create table which contains as many column as many name
 of
> activities there are on my table of activities.
> If I manage to create it I start fill this table cell by cell using
 two
> or three cursors I've already created
>
> So ... what do you think ? Now is it clear ?
> Ir maybe You have other ideas how to convert "oracle result" to
 "excel
> sheet"
>
>
> K.
>
>
>
>
>
>
>
>
> Guido Konsolke wrote:
> > "Krzys!" <krzys78_at_polbox.com> schrieb im Newsbeitrag
> > news:bgd2nq$1oi5$1_at_foka.acn.pl...
> >
> >>True :-) I used to working with TSQL and I'm a begginer in
 Oracle.
> >>Anyway I try to figure out with temporary tables but if someone
 else
> >>have other suggestion how to solve my problem please help !!!!
> >>
> >>
> >>K.
> >
> >
> > Hi Krzys,
> >
> > we would try to help you if you let us know what
> > your problem is. Please elaborate. Oh: and
> > include your dbms version (at least!).
> >
> > Greetings,
> > Guido
> >
> >
>
Received on Mon Aug 04 2003 - 11:12:58 CEST