combining columns into one table

What is the easiest way to combine the output of a several selects on a tabl
e and have each output become a column on a new table?
Thanks
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 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 r
esult
container
and then execute "select desc from A where oid=1 and desc=furniture" -- wi
th 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 b
ut I hope I am getting the concept across.
Thanks
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 dyna
mic 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, ther
e really is no relation between cells on the same row.
But as a general question is it possible to manufacture a table where a colu
mn 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 valu
es) 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|||Joel,
A table consists of a number of rows, where each row has the same column str
ucture and datatype. Let's break
down your last paragraph:
<<But as a general question is it possible to manufacture a table where a co
lumn 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 i
n 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 wher
e 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 valu
es for each column in a row is
restricted by the datatype that the column has, and a column can also possib
ly 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 d
atabase and also the result of
SELECT statements) with presentation of the data (what you do in a client ap
plication).
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 we
b 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 r
ow.
> 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 whe
n one column (with all rows
containing values) to be added is longer that the table to be added to, the
n will extra rows (which can be
empty) be added so that all columns have same number of rows?
> Thanks
> Joel