how can i get a 1 row result set having multiple rows joined into 1 row
if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)
thx
samhamWant to show us the query? What would make them join together?|||see Using COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)
rudy
http://r937.com/|||He wants to combine multiple rows..
And you don't use COALESCE to build a comma delimited srting..
Is used so that any null value in the string does not blow away the results...
It's the ability to do SELECT @.x = @.X + col1
like...
DECLARE @.x varchar(8000)
SELECT @.x = ISNULL(@.x,'') + ISNULL(FirstName,'') FROM Employees
SELECT @.x
The coalesec trick allows you to eliminmate commas if the value in the column is Null
so you dont get 1,,2,3,4,,5|||He wants to combine multiple rows yes, he wants the values from multiple rows to be put into a comma-delimited string
And you don't use COALESCE to build a comma delimited srting damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
The coalesec trick allows you to eliminmate commas if the value in the column is Null yes, that's correct, that's what it does for the first row
rudy|||thx guys that's exactly what i wanted
i'll use the code from the article
DECLARE @.EmployeeList varchar(100)
SELECT @.EmployeeList = COALESCE(@.EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @.EmployeeList
--Results--
---
1, 2, 4
Showing posts with label selects. Show all posts
Showing posts with label selects. Show all posts
Sunday, March 25, 2012
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?
Thanks
Joel
Assuming 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 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
|||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, 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 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
|||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
sqlsql
Thanks
Joel
Assuming 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 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
|||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, 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 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
|||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
sqlsql
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
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
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
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
Subscribe to:
Posts (Atom)