dynamic table creation

**Jim Kennedy** · Jun 27 '08, 05:00 PM

Re: dynamic table creation

"Krzys!" <krzys78@polbox .comwrote in message
news:bgb7kv$13t n$1@foka1.acn.p l...

I'd like to create a table dynamicly. List of column in this table
should be taken from select: "select distinct fiel from table "
>
How to do it ?
>
>
tnx in advance for help
K.
>
>
>
>
--
U¿ywam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org
>

It is rare that in Oracle you would ever need to do this. Usually people
who feel they need to do this are from a SQLServer or Sybase background. If
you feel that you must do this look up global temprorary tables in the
documentation.
Jim

**Rauf Sarwar** · Jun 27 '08, 05:00 PM

Re: dynamic table creation

"Jim Kennedy" <kennedy-down_with_spamm ers@no_spam.com cast.netwrote in message news:<E3aWa.195 02$cF.8390@rwcr nsc53>...

"Krzys!" <krzys78@polbox .comwrote in message
news:bgb7kv$13t n$1@foka1.acn.p l...

I'd like to create a table dynamicly. List of column in this table
should be taken from select: "select distinct fiel from table "

How to do it ?

tnx in advance for help
K.

--
U¿ywam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org

It is rare that in Oracle you would ever need to do this. Usually people
who feel they need to do this are from a SQLServer or Sybase background. If
you feel that you must do this look up global temprorary tables in the
documentation.
Jim

To add to above and just so that you know that it CAN be done in
Oracle, Visit http//tahiti.oracle.c om and lookup dynamic sql. Use
dbms_sql search keyword.

Regards
/Rauf Sarwar

**sybrandb@yahoo.com** · Jun 27 '08, 05:01 PM

Re: dynamic table creation

"Krzys!" <krzys78@polbox .comwrote in message news:<bgd2nq$1o i5$1@foka.acn.p l>...

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.
>
>

You got that suggestion didn't you. 'Look up global temporary tables
in the documentation'.
You won't learn Oracle without reading the documentation, so I suggest
you start reading it now.
(Or don't come back in this forum)

Senior Oracle DBA

**Guido Konsolke** · Jun 27 '08, 05:01 PM

Re: dynamic table creation

"Krzys!" <krzys78@polbox .comschrieb im Newsbeitrag
news:bgd2nq$1oi 5$1@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

**Jim Kennedy** · Jun 27 '08, 05:01 PM

Re: dynamic table creation

"Krzys!" <krzys78@polbox .comwrote in message
news:bgd2nq$1oi 5$1@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.
>
>
>
Jim Kennedy wrote:

"Krzys!" <krzys78@polbox .comwrote in message
news:bgb7kv$13t n$1@foka1.acn.p l...

>I'd like to create a table dynamicly. List of column in this table
>should be taken from select: "select distinct fiel from table "
>
>How to do it ?
>
>
>tnx in advance for help
>K.
>
>
>
>
>--
>U¿ywam MozilliPL, a Ty? MozillaPL: http://www.MozillaPL.org
>

It is rare that in Oracle you would ever need to do this. Usually

people

who feel they need to do this are from a SQLServer or Sybase background.

If

you feel that you must do this look up global temprorary tables in the
documentation.
Jim

>

Ah, resorting to bad habits. In those environments you have to use
temporary tables. In Oracle just try selecting the data, don't bother with
temporary tables. It will only slow you down. In all my years in using
Oracle - dba and application developer and it is over 10 years of
experience - I have only come accros one instance where global temporary
tables where the answer. At the time they did not exist, so we just used a
regular table and created it once for everyone.

Oracle!=SQLServ er. About 2 different beasts as you can have. In Oracle
readers don't block writers and writers don't block readers. No really they
don't. You have a lot to learn that things are done much easier in Oracle.
Jim

**Guido Konsolke** · Jun 27 '08, 05:01 PM

Re: dynamic table creation

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@polbox .comschrieb im Newsbeitrag
news:bgdejv$1rd 4$1@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='PO L'
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@polbox .comschrieb im Newsbeitrag
news:bgd2nq$1oi 5$1@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

>

**Krzys!** · Jun 27 '08, 05:02 PM

Re: dynamic table creation

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@polbox .comschrieb im Newsbeitrag
news:bgdejv$1rd 4$1@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='PO L'
>>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@polbox .comschrieb im Newsbeitrag
>>>news:bgd2nq$ 1oi5$1@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
>>>
>>>

>>

>
>

**sybrandb@yahoo.com** · Jun 27 '08, 05:02 PM

Re: dynamic table creation

"Krzys!" <krzys78@polbox .comwrote in message news:<bgo0e3$26 rl$1@foka.acn.p l>...

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.
>

And you will end up with a mess, of course. But you seem to love
'experimenting until it works' as opposed to 'doing it properly'
And above all you don't want to RTFM. Go figure!

Sybrand Bakker
Senior Oracle DBA'

dynamic table creation

dynamic table creation

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment