Answered by:
How to Merge a Table

-
I am having Table1 as Main Account and Sub Account with Account No. First 8 digits are same. Now, I have to create a Table 2 with all the Main Account as it is but the Sub Account (end with 03) having a single Row with [Mkt_val] equal to some of Sub Account Mkt Value and also [Asset_Val] having some of all (Main Account and Sub Account).
I don't have any idea how to start with. Thanks for your help.
Table1 Acct Numb Account Type Asset Mkt Val Acct Asset MV 881009201 Main Account 30 120 881009201 Main Account 40 120 881009201 Main Account 20 120 881009201 Main Account 30 120 881009203 Sub Account 50 80 881009203 Sub Account 10 80 881009203 Sub Account 20 80 Table2 Acct Numb Account Type Asset Mkt Val Acct Asset MV 881009201 Main Account 30 200 881009201 Main Account 40 200 881009201 Main Account 20 200 881009201 Main Account 30 200 881009201 Sub Account 80 200
I am Having a Table1 with the information given above. I am having 2 types of account:
1. Main Account (ending with 01)
2. Sub Account (ending with 03)
Now, I need a table with Having All the Main Account. But All the Sub Account Club into a Single Account No. Whose Account no. will be same as of its Main Account No. but the Account Type wii be [Sub Account].
Now we have 2 more colums:
1. Asset Mkt Val
2. Acct Asset MV
In Table 1: The [Acct Asset MV] shows the sum of Main Account ( 30+ 40+ 20+ 30= 120) and [Asset Mkt Val] shows the Sum of Sub Accont (50+10+20 = 80).
Now in Table 2: I want to have [Acct Asset MV] should be some of all account =(120+80= 200) and in Sub Account the [Asset Mkt Val] should be 80.
- Edited by njain8 Tuesday, June 25, 2013 4:59 PM More Explanation
Question
Answers
-
Hi njain8,
There are some steps, you can refer to them.
create table table1( AcctNumb int not null, AccountType varchar(50) not null, AssetMktVal int not null, AcctAssetMV int not null) ----create a table1 insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values(881009201,'Main Account',30,120); insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values(881009201,'Main Account',40,120), (881009201,'Main Account',20,120), (881009201,'Main Account',30,120), (881009203,'Sub Account',50,80), (881009203,'Sub Account',10,80), (881009203,'Sub Account',20,80) ---insert records into table1 Select * from Table1 ---check table1 records ---Create the table2 by copying Select * into table2 from table1 ----check table2 Select * from table2 Update table2 set AcctAssetMV=(Select sum(AssetMktVal) from table2) ----update AcctAssetMV in table2 Select * from table2 ---Add a record insert into table2(AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values ((select distinct AcctNumb from table2 where AccountType='Main Account'),'sub Account', (select SUM (AssetMktVal) from table2 where AccountType='Sub Account'),(select distinct AcctAssetMV from table2 where AccountType='Sub Account')) ----Delete the unnecessary records delete from table2 where AcctNumb not in(select AcctNumb from table2 where AccountType='Main Account') ---result of table2 AcctNumb AccountType AssetMktVal AcctAssetMV 881009201 Sub Account 80 200 881009201 Main Account 30 200 881009201 Main Account 40 200 881009201 Main Account 20 200 881009201 Main Account 30 200
Thanks,
Sofiya LiSofiya Li
TechNet Community Support- Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Monday, July 01, 2013 1:37 AM
- Marked as answer by Allen Li - MSFTModerator Wednesday, July 03, 2013 6:09 AM
All replies
-
Of course I don't know what you are doing, but I suspect you probably want to separate the main account records and the sub account records into two different tables. Then it will be easier to do this type of thing.
As it is, I think you want to create two virtual tables, something like:
SELECT SUM([Asset Mkt Val]
FROM Table1
WHERE [Account Type] = 'Sub Account'
GROUP BY [Acct Numb];Then you need to add the Main Account number to that sub account table by removing the 03 from the Acct Numb and replacing it with 01. Then you can UNION with the other records from Table1 (WHERE [Account Type] = Main Account') and do a sum of everything, grouping by Acct Numb.
So much easier if you put different types of items (Main and Sub accounts) into different tables. It's much easier to add and join tables together than it is too split tables apart once the different types of data have already been mixed together.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
-
hi ,
i cant understood can you pls elaborate your query with pictorial representation ?
- Edited by aravindhrm Tuesday, June 25, 2013 4:26 PM need some updation
-
Hi,
I am Having a Table1 with the information given above. I am having 2 types of account:
1. Main Account (ending with 01)
2. Sub Account (ending with 03)
Now, I need a table with Having All the Main Account. But All the Sub Account Club into a Single Account No. Whose Account no. will be same as of its Main Account No. but the Account Type wii be [Sub Account].
Now we have 2 more colums:
1. Asset Mkt Val
2. Acct Asset MV
In Table 1: The [Acct Asset MV] shows the sum of Main Account ( 30+ 40+ 20+ 30= 120) and [Asset Mkt Val] shows the Sum of Sub Accont (50+10+20 = 80).
Now in Table 2: I want to have [Acct Asset MV] should be some of all account =(120+80= 200) and in Sub Account the [Asset Mkt Val] should be 80.
-
Hi njain8,
There are some steps, you can refer to them.
create table table1( AcctNumb int not null, AccountType varchar(50) not null, AssetMktVal int not null, AcctAssetMV int not null) ----create a table1 insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values(881009201,'Main Account',30,120); insert into Table1( AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values(881009201,'Main Account',40,120), (881009201,'Main Account',20,120), (881009201,'Main Account',30,120), (881009203,'Sub Account',50,80), (881009203,'Sub Account',10,80), (881009203,'Sub Account',20,80) ---insert records into table1 Select * from Table1 ---check table1 records ---Create the table2 by copying Select * into table2 from table1 ----check table2 Select * from table2 Update table2 set AcctAssetMV=(Select sum(AssetMktVal) from table2) ----update AcctAssetMV in table2 Select * from table2 ---Add a record insert into table2(AcctNumb,AccountType,AssetMktVal,AcctAssetMV) values ((select distinct AcctNumb from table2 where AccountType='Main Account'),'sub Account', (select SUM (AssetMktVal) from table2 where AccountType='Sub Account'),(select distinct AcctAssetMV from table2 where AccountType='Sub Account')) ----Delete the unnecessary records delete from table2 where AcctNumb not in(select AcctNumb from table2 where AccountType='Main Account') ---result of table2 AcctNumb AccountType AssetMktVal AcctAssetMV 881009201 Sub Account 80 200 881009201 Main Account 30 200 881009201 Main Account 40 200 881009201 Main Account 20 200 881009201 Main Account 30 200
Thanks,
Sofiya LiSofiya Li
TechNet Community Support- Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Monday, July 01, 2013 1:37 AM
- Marked as answer by Allen Li - MSFTModerator Wednesday, July 03, 2013 6:09 AM