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
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||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...
>
>