Re: dynamic table creation

From: Krzys! <krzys78_at_polbox.com>
Date: Tue, 05 Aug 2003 12:22:26 +0200
Message-ID: <bgo0e3$26rl$1_at_foka.acn.pl>

Damn .. it's so easy !!!! No cursors, no store procedures , just one simple query with case :) I owe You one beer ;)

Thanks a lot !!
K.

Guido Konsolke wrote:

> 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 Tue Aug 05 2003 - 12:22:26 CEST