Grouped rows inserted into columns

[baaul]

I need to INSERT rows from #temp table into #final table with grp_code and bap_id combination. Thanks.



Create Table #tmp (
sort_id int default 0,
grp_Code int default 1,
business_activity_profile_id INT,
business_activity_product_id int default 0,
value1_tx varchar(500) default NULL,
value2_tx varchar(500) default NULL
)


Data in the #temp table:

Sort_id grp_code bap_id Value1_tx Value2_tx

1 1 1 Alabama NULL
1 1 1 Alaska NULL
1 1 1 Arizona NULL
1 1 1 California NULL
2 1 2 Alaska NULL
2 1 2 Arizona NULL
2 1 2 Arkansas NULL
2 1 2 California NULL
3 1 3 Alaska NULL
3 1 3 Arizona NULL
3 1 3 Arkansas NULL
1 2 1 ACS Image Solutions NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 ACS Unclaimed Propery Clearinghouse, Inc. NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 Acxiom NULL
1 2 1 ABC Virtual Communications, Inc NULL
2 2 2 ABC Virtual Communications, Inc NULL
2 2 2 ACS Unclaimed Propery Clearinghouse, Inc. NULL
2 2 2 #NAME? NULL
2 2 2 ACS Image Solutions NULL
2 2 2 Acxiom NULL
2 2 2 ADP NULL
2 2 2 ADP Investor Communication NULL
2 2 2 Bancomer Transfer Services NULL
3 2 3 ABC Virtual Communications, Inc NULL
3 2 3 ACS Image Solutions NULL
3 2 3 ACS Unclaimed Propery Clearinghouse, Inc. NULL
3 2 3 Acxiom NULL
3 2 3 ABC Virtual Communications, Inc NULL
3 2 3 ACS Image Solutions NULL
35 3 47 STATE AND LOCAL LAWS AND REGULATIONS Insurance Laws & Regulations - Various States
35 3 47 STATE AND LOCAL LAWS AND REGULATIONS Escheat Laws
35 3 47 STATE AND LOCAL LAWS AND REGULATIONS State Credit Law Requirements
35 3 47 STATE AND LOCAL LAWS AND REGULATIONS State Deceptive Practices Laws
35 3 47 OTHER FEDERAL LAWS AND REGULATIONS American's with Disability Act Public Law 101-336 - Abstract
35 3 47 OTHER FEDERAL LAWS AND REGULATIONS Bankruptcy Laws
35 3 47 OTHER FEDERAL LAWS AND REGULATIONS COBRA
35 3 47 OTHER FEDERAL LAWS AND REGULATIONS Department of Labor Rules




Create Table #final (
sort_id INT,
bap_id INT,
geography varchar(120) NULL,
vendor_nm varchar(250) NULL,
regulator varchar(100) NULL,
regulation varchar(500) NULL
)



This is how it needs to be inserted into #final table:

rows from value1_tx inserted into Column 'geography' WHERE grp_code =1 and bap_id = 1
rows from value1_tx inserted into Column 'vendor_nm' WHERE grp_code =2 and bap_id = 1
rows from value1_tx inserted into Column 'regulator'WHERE grp_code =3 and bap_id = 1
rows from value2_tx inserted into Column 'regulation' WHERE grp_code =3 and bap_id = 1