I created a query that got the following result.
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
join DW_ODS.dbo.Customer_Header_Tbl b
on a.Customer_Nbr_Txt=b.Customer_Nbr_Txt
join dw_ods.dbo.Customer_Address_Tbl c
on b.Cust_Nbr_Key=c.Cust_Nbr_Key
where (a.Legal_Name_Txt like '% LLC%'
or a.Legal_Name_Txt like '%L.L.C%'
or a.Legal_Name_Txt like '%L.C%')
and a.Legal_Entity_Struct_Txt not like '%Limited Liability%'
AND a.Current_Row_Flag='Y'
and a.Source_System_Code_Txt='WMS'
order by 1
The sample result is like the following:
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that? Thank you very much!