Create columns from data

[Stedo]

hi,

I want to take some data from a query and create a table from it. Something like this:

SELECT values FROM tableA;

... now create a table with columns represented by the returned values.

Is this possible?? I'm using Oracle 10g.

Thanks for any help.

Steve

 

[hoinz]

Yes, quite easy:

create table_b as
select column1, column2 from table_a;

or if you prefer to change column names:

create table_b as
select column1 as newname1, column2 as newname2 from table_a;

 

[Stedo]

hi,

That's not really what I mean, for example I have the following table with the following values:

Table: Properties

Name | Value
=======================
TYPE | High
TYPE | Low
NAME | Module Error
NAME | Integration Error

What I want to be able to do is create a table or query which returns:

TYPE | NAME
========================
High | Module Error
Low | Integration Error

Is it possible?

Thanks
Steve

 

[hoinz]

Ok, I totally misunderstood your question.
Not sure if I understand better now...

Main problem:
How can we (or Oracle) know that you do not want this result:

TYPE | NAME
========================
Low | Module Error
High | Integration Error

So I guess the answer to your question is a no.

And please do not tell us that you want the data in the same order as in your input data.
There is no such thing as a natural order in an Oracle table.


regards

 

[Stedo]

hi,

I suspected as much, the real problem comes from an application we have which stores user defined fields in a structure similar to a properties table. What we want to do is have these transformed into a table like structure, while at the same time maintaining the flexibility of property table structure. Trouble being that we don't know how many property fields there are, as this is user-definable?

Thanks for your help
Stephen

 

[hoinz]

Another idea, perhaps this might help:
Does your application provide additional output, that may be considered as some kind of unique id, a timestamp perhaps?
You could solve your problem, if you have a table like this:

ID | Name | Value
====================================
1234 | TYPE | High
1235 | TYPE | Low
1234 | NAME | Module Error
1235 | NAME | Integration Error

regards

 

[Stedo]

hi,

Yes, there is a unique ID however the problem lies in that we have fields listed in a property table which are defined by the user. There can be a good many of these fields and these are stored as data rather than structure. For example,

we have Product ID 11 and Product ID 22, these products have many properties which the user has defined, for example a Width, Bredth and Height. This is defined in the database as follows:

ID | PROPERTY_NAME | PROPERTY_VALUE
==========================================
11 WIDTH 100
11 BREDTH 200
11 HEIGHT 300
22 WIDTH 50
22 BREDTH 55
22 HEIGHT 88

As you can see searches on the properties will be very long. What we want to do is transfer this table into a structure as follows:

ID | WIDTH | BREDTH | HEIGHT
========================================
11 100 200 300
22 50 55 88

Obviously much faster for searching, but we want to do this dynamically as we want to preserve the flexibility in our system. So for example, on system startup we transform the properties table into a properly formatted table as above.

Thanks for your help
Stephen

 

[hoinz]

Hello,

from these additional information I gather that surely it is possible.
But depending on how much flexibility and dynamics you need, complexity (and development time, and costs) may converge to infinity ...

Not sure if a tool like this already exists; might be a business opportunity though ...
You might want to look out for such a tool.

And if you are going to build your own, I think you should try to find a compromise between your users and your budget director.

One imporant question seems to me:
Whenever one of your users adds a new property, let's say 'weight', and adds a weight value for ID 11, what should happen then?
Has there to be a new table without any other human intervention, immediately or on next startup? If so, reading about Dynamic SQL may be useful.
-Or-
Is it enough, if a person with some knowledge in databases will create a table and a query afterwards?
(Perhaps this could preserve you a permanent job? )

hope this helps