I created a query that got the following result.
FROM dw_mart.dbo.DimCustomer a
join DW_ODS.dbo.Customer_Header_Tbl b
join dw_ods.dbo.Customer_Address_Tbl c
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%'
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!