combining columns into one table

What is the easiest way to combine the output of a several selects on a table and have each output become a column on a new table
Thank
JoelAssuming each SELECT has the same output and that the datatypes match:
INSERT newtable
SELECT col = <...some_select...>
UNION ALL
SELECT <...some_other_select...>
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:CFA8303B-D2E0-4746-BBA4-04FA61743685@.microsoft.com...
> What is the easiest way to combine the output of a several selects on a
table and have each output become a column on a new table?
> Thanks
> Joel|||well not exactly what I wanted - here's what I'm looking for. for example, suppose you have one table A with 3 columns as shown below
oid name des
-- -- --
1 vase containe
2 lamp ligh
1 desk furnitur
2 table furnitur
1 table furnitur
1 lamp ligh
then execute "select desc from A where oid=1 and desc=container" -- with resul
containe
and then execute "select desc from A where oid=1 and desc=furniture" -- with resul
furnitur
furnitur
what I want to do is combine both outputs into 2 columns like this
container furnitur
furnitur
furnitur
Actually the queries and tables are more involved than this simple example but I hope I am getting the concept across
Thank
Joel|||This looks like a report of some kind, and the relationship here is not,
well, relational... probably better to iterate through and combine things
together at the client.
I don't see exactly how container ends up being directly related to
furniture and why furniture has three rows (one associated with container
and two not).
Can you provide REAL table schema, REAL sample data, and REAL desired
results? See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:3D60D589-51C3-4586-BF44-9FFE063AD95B@.microsoft.com...
> well not exactly what I wanted - here's what I'm looking for. for
example, suppose you have one table A with 3 columns as shown below:
> oid name desc
> -- -- --
> 1 vase container
> 2 lamp light
> 1 desk furniture
> 2 table furniture
> 1 table furniture
> 1 lamp light
> then execute "select desc from A where oid=1 and desc=container" -- with
result
> container
> and then execute "select desc from A where oid=1 and desc=furniture" --
with result
> furniture
> furniture
> what I want to do is combine both outputs into 2 columns like this:
> container furniture
> furniture
> furniture
> Actually the queries and tables are more involved than this simple example
but I hope I am getting the concept across.
> Thanks
> Joel|||You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that I could build the table and then the client (ColdFusion) would iterate thru each row and present the row values via an HTML table. And yes, there really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a column is added to the table thereby increasing the number of columns by 1 each time a column is added? Also when one column (with all rows containing values) to be added is longer that the table to be added to, then will extra rows (which can be empty) be added so that all columns have same number of rows
Thank
Joel|||Joel,
A table consists of a number of rows, where each row has the same column structure and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added?>>
Yes. If the table is a stored table, you do "ALTER TABLE tblname ADD colname ...". If the table is a result
from a SELECT statement, then you define that structure by the column list in the SELECT statement.
<<Also when one column (with all rows containing values) ...>>
"All rows containing values" is always true in a table. You never have a row which "doesn't contain values".
<<...to be added is longer that the table to be added to...>>
What is "longer" than what? Again, a table consists of a number of rows where each row has the same column
structure.
<<..., then will extra rows (which can be empty)...>>
There is no such thing as an empty row. That concept doesn't exist. The values for each column in a row is
restricted by the datatype that the column has, and a column can also possibly be NULL.
<<... be added so that all columns have same number of rows?>>
? A column doesn't "have a number of rows". A table is defined by a datatype for each column, and for each
row, you have a value for each column in the table.
I agree with Aaron that you seem to confuse data (what we have stored in a database and also the result of
SELECT statements) with presentation of the data (what you do in a client application).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"joel" <anonymous@.discussions.microsoft.com> wrote in message
news:5BE36CEC-27A7-4E75-8C2C-72C8A104E5E6@.microsoft.com...
> You're right, it is a report that will be displayed via ColdFusion on a dynamic web page. I was hoping that
I could build the table and then the client (ColdFusion) would iterate thru each row and present the row
values via an HTML table. And yes, there really is no relation between cells on the same row.
> But as a general question is it possible to manufacture a table where a column is added to the table thereby
increasing the number of columns by 1 each time a column is added? Also when one column (with all rows
containing values) to be added is longer that the table to be added to, then will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel