How to Merge a Table

All replies

• 

  

  0

  Sign in to vote

  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

  Tuesday, June 25, 2013 3:57 PM

  Reply

  |

  Quote

  Answerer
• 

  

  0

  Sign in to vote

  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

  Tuesday, June 25, 2013 4:24 PM

  Reply

  |

  Quote
• 

  

  0

  Sign in to vote

  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. 

  Tuesday, June 25, 2013 4:58 PM

  Reply

  |

  Quote
• 

  

  1

  Sign in to vote

  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 Li
  

  ---

  Sofiya 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 - MSFTMicrosoft contingent staff, Moderator Wednesday, July 03, 2013 6:09 AM

  Thursday, June 27, 2013 7:54 AM

  Reply

  |

  Quote

  Moderator