How to add new columns ???

Question

I have just created a query as below

select
a.business_partner_id,
a.marketing_material_ind,
a.legal_status_cde,
a.share_information_ind,
a.inserted_by,
a.business_partner_nme,
a.execution_dte,
a.business_partner_nbr,
a.sued_ind,
a. exposure_ind,
a. collection_letter_ind,
a.PMS_dealer_nbr,
a.trade_nme,
a.default_ind,
a.customer_typ,
a.business_partner_nme_mlg,
a.BP_cofc_cde,
a.business_partner_nbr_rpt,
a. APPLICATION_RUNNING_NBR,

b.business_partner_id,
b.address_seq,
b.address_type_cde,
b.country_cde,
b.state_cde,
b.city_cde,
b.street_type_cde,
b.street_nme,
b.postal_cde,
b.building_nme,
b.street_nbr,
b.unit_nbr,
b.POB_ind,
b.POB_add,
b.stay_in_yy,
b.stay_in_mm,
b.execution_dte,
b.inserted_by,
b.attention_to_nme,
b.street_address_dsc,
b.international_address_ind,
b.tamborn_dsc_mlg,
b.amphur_dsc_mlg,

c.business_partner_id,
c.phone_nbr,
c.phone_seq,
c.country_cde,
c.area_cde,
c.phone_type_cde,
c.address_seq,
c.extension_nbr,
c.execution_dte,
c.inserted_by,
c.application_report_ind,
c.settlement_authority_ind,
c.Priority_ind,

d.business_partner_id,
d.role_cde,
d.role_from_dte,
d.role_to_dte,
d.correspondence_ind,
d.execution_dte,
d.inserted_by,
d.approved_ind,
d.chart_priority_rnk,
d.activate_ind,
d.self_reg_ind,
d.self_ins_ind,
d.Insurance_comm_pct,
d.asset_registration_ind,
d.bp_fc_ind,
d.reg_code,
d.reg_date,
d.reg_exp_date,
d.tax_reg_nbr,
d.tax_reg_certificate_nbr,

e.contract_id,
e.business_partner_id,
e.role_cde,
e.correspondence_ind,
e.execution_dte,
e.inserted_by,
e.loan_card_nbr,




from  bp_main a ,    
      bp_address b ,
      bp_phone c,
      bp_role d,
      contract_bp e

where
a.business_partner_id = b.business_partner_id
and
b.business_partner_id= c.business_partner_id
and
a.business_partner_id= d.business_partner_id
and
a.business_partner_id = e.business_partner_id
and
e.role_cde = d.role_cde

Explanation:

I've five tables as you can see from above query i  joined all of them, join condition applied perfectly,now after joining , there is a field

role_cde (which is common in both last two tables)

Requirement:

I need a query which will create new columns based on role_cde, for example:

after the joining applied

when Role_cde = '00001' then a new column will  be created having a name 'Dealer',  and all previous columns will also be showing but this new column will also be added.

similarly,

when Role_cde= '00002' then a new column will create having a name 'Borrower', and all previous columns also showing....

hope this will make some understanding...

Answer 1

 use 'if' condition with 'or' inside with column names
and pl look at this link also

http://jahaines.blogspot.in/2009/12/sql-server-2005-how-to-move-10-millions.html

Answer 2

Hi Faisalkyo,

Based on your scenario, you can create a stored procedure to return the result set based on the Role_cde value passed to the procedure. The code is as follows:

create proc TestJoin 
@Role_cde int 
as 
declare @columnName varchar(20); 
declare @sql varchar(1000); 
if @Role_cde=00001 
begin 
set @columnName='Dealer'; 
end 
else if @Role_cde=00002 
begin 
set @columnName='Borrower'; 
end 
set @sql='select 
a.business_partner_id,
 a.marketing_material_ind,
 a.legal_status_cde,
 a.share_information_ind,
 a.inserted_by,
 a.business_partner_nme,
 a.execution_dte,
 a.business_partner_nbr,
 a.sued_ind,
 a. exposure_ind,
 a. collection_letter_ind,
 a.PMS_dealer_nbr,
 a.trade_nme,
 a.default_ind,
 a.customer_typ,
 a.business_partner_nme_mlg,
 a.BP_cofc_cde,
 a.business_partner_nbr_rpt,
 a. APPLICATION_RUNNING_NBR,
 
b.business_partner_id,
 b.address_seq,
 b.address_type_cde,
 b.country_cde,
 b.state_cde,
 b.city_cde,
 b.street_type_cde,
 b.street_nme,
 b.postal_cde,
 b.building_nme,
 b.street_nbr,
 b.unit_nbr,
 b.POB_ind,
 b.POB_add,
 b.stay_in_yy,
 b.stay_in_mm,
 b.execution_dte,
 b.inserted_by,
 b.attention_to_nme,
 b.street_address_dsc,
 b.international_address_ind,
 b.tamborn_dsc_mlg,
 b.amphur_dsc_mlg,
 
c.business_partner_id,
 c.phone_nbr,
 c.phone_seq,
 c.country_cde,
 c.area_cde,
 c.phone_type_cde,
 c.address_seq,
 c.extension_nbr,
 c.execution_dte,
 c.inserted_by,
 c.application_report_ind,
 c.settlement_authority_ind,
 c.Priority_ind,
 
d.business_partner_id,
 d.role_cde,
 d.role_from_dte,
 d.role_to_dte,
 d.correspondence_ind,
 d.execution_dte,
 d.inserted_by,
 d.approved_ind,
 d.chart_priority_rnk,
 d.activate_ind,
 d.self_reg_ind,
 d.self_ins_ind,
 d.Insurance_comm_pct,
 d.asset_registration_ind,
 d.bp_fc_ind,
 d.reg_code,
 d.reg_date,
 d.reg_exp_date,
 d.tax_reg_nbr,
 d.tax_reg_certificate_nbr,
 
e.contract_id,
 e.business_partner_id,
 e.role_cde,
 e.correspondence_ind,
 e.execution_dte,
 e.inserted_by,
 e.loan_card_nbr,
[' + @columnName + ']
from   bp_main a ,    
       bp_address b ,
       bp_phone c,
       bp_role d,
       contract_bp e
where 
a.business_partner_id = b.business_partner_id
 and 
b.business_partner_id= c.business_partner_id
 and 
a.business_partner_id= d.business_partner_id
 and 
a.business_partner_id = e.business_partner_id
 and 
e.role_cde ='+@ID+'
and 
d.role_cde='+@ID
 ; 
EXEC(@sql) 

Then, you can create a dataset based on the stored procedure in the Query Designer. When executing the stored procedure in the Query Designer, the parameter Role_cde prompt will appear. Whether the "Dealer" column or the "Borrower" column is added to the dataset depends on the parameter value you specify for @Role_cde.

Besides, there is another approach on the report side. We can select both "Dealer" and "Borrower" columns when creating the dataset so that the dataset includes both "Dealer" and "Borrower" fields. When designing the report, we can use expression to control the field value or control the column visibility. For example, we can use the expression "=IIf(Fields!Role_cde=00001, nothing, Fields!Borrower.Value)" to replace the expression "=Fields!Dealer.Value".

Hope this helps.

Regards,

---

Mike Yin
TechNet Community Support