July 11, 2013 at 6:02 am
Create Table #Customer_Profile_Master1
(
Lnno Varchar(15),
Co_apcode_1_categ Varchar(200),
Co_apcode_2_categ Varchar(200),
Co_apcode_3_categ Varchar(200),
Co_apcode_4_categ Varchar(200)
)
Insert Into #Customer_Profile_Master1(Lnno)
Values ('1')
Select * from #Customer_Profile_Master1
Drop table #Temp1
Create Table #Temp1
(
Lnno Varchar(15),
CustCode Varchar(200),
Co_apcode Varchar(200),
Category Varchar(200),
Rownumber Int
)
Insert Into #Temp1
Values ('1','M0000036','A0000026','SALARIED',1),
('1','M0000036','A0000027','SELF EMPLOYED PROFESSIONALS',2),
('1','M0000036','A0000028','STUDENT',3)
Select * from #Temp1
My Requirement is I want to update in #Customer_Profile_Master1 table from #Temp1 as
Co_apcode_1_categ = For that Lnno if Rownumber = 1 then corresponding Category should be updated
Co_apcode_2_categ = For that Lnno if Rownumber = 2 then corresponding Category should be updated
Co_apcode_3_categ = For that Lnno if Rownumber = 3 then corresponding Category should be updated
Co_apcode_4_categ = For that Lnno if Rownumber = 4 then corresponding Category should be updated
I had tried this way but it is not working for me,
Update #Customer_Profile_Master1
Set Co_apcode_1_categ = Case when B.Rownumber = 1 then B.Category else '' End,
Co_apcode_2_categ = Case when B.Rownumber = 2 then B.Category else '' End,
Co_apcode_3_categ = Case when B.Rownumber = 3 then B.Category else '' End,
Co_apcode_4_categ = Case when B.Rownumber = 4 then B.Category else '' End
From #Customer_Profile_Master A Join #Temp1 B
On A.Lnno = B.Lnno
Please help me as I need this in urgent.
Thanks in Advance!
July 11, 2013 at 6:14 am
I don't think this is the fastest solution, but it will work:
Select * from #Temp1
Select * from #Customer_Profile_Master1
Update #Customer_Profile_Master1
set Co_apcode_1_categ = (select top 1 Category from #temp1 where rownumber = 1 and cpm.lnno = lnno)
, Co_apcode_2_categ = (select top 1 Category from #temp1 where rownumber = 2 and cpm.lnno = lnno)
, Co_apcode_3_categ = (select top 1 Category from #temp1 where rownumber = 3 and cpm.lnno = lnno)
, Co_apcode_4_categ = (select top 1 Category from #temp1 where rownumber = 4 and cpm.lnno = lnno)
from #Customer_Profile_Master1 cpm
Select * from #Customer_Profile_Master1
July 11, 2013 at 6:28 am
Another solution is to PIVOT the #temp1 table and join this with the table you want to update
-- Pivot source table and join with table to update
Update #Customer_Profile_Master1
set Co_apcode_1_categ = [1]
, Co_apcode_2_categ = [2]
, Co_apcode_3_categ = [3]
, Co_apcode_4_categ = [4]
FROM (
SELECT lnno
, Category
, Rownumber
FROM #temp1
) AS SourceTable
PIVOT(MAX(Category) FOR Rownumber IN (
[1]
, [2]
, [3]
, [4]
)) AS PivotTable
inner join #Customer_Profile_Master1
on PivotTable.lnno = #Customer_Profile_Master1.lnno
July 11, 2013 at 6:42 am
Thanks a Lot!
Its worked for me!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply