Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>