none
How to Merge a Table

    Question

  • 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
    Tuesday, June 25, 2013 1:56 PM

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 Li

    Sofiya Li
    TechNet Community Support

    Thursday, June 27, 2013 7:54 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

    Tuesday, June 25, 2013 3:57 PM
  • 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
  • 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
  • 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

    Thursday, June 27, 2013 7:54 AM