importing customer vendor addresses with roles from AX to D365FO Microsoft Dynamics 365 Finance and Operations

importing customer vendor addresses with roles from AX to D365FO  Microsoft Dynamics 365 Finance and Operations

in many data migration projects we need to migrate addresses and one of common problem is getting single address with multiple roles imported in D365FO in a simple way.

sharing one such of my learning for SQL 2017+ and SQL 2016- versions:

SELECT VT.ACCOUNTNUM as VENDORACCOUNTNUMBER,  VT.DATAAREAID as VENDORLEGALENTITYID,

--SQL 2017 (select STRING_AGG(LOGISTICSLOCATIONROLE.NAME, ';')  from LOGISTICSLOCATIONROLE where  DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID) AS ADDRESSLOCATIONROLES,

     STUFF(( SELECT ';' + LOGISTICSLOCATIONROLE.NAME FROM LOGISTICSLOCATIONROLE

LEFT JOIN DIRPARTYLOCATIONROLE ON  DIRPARTYLOCATIONROLE.PARTYLOCATION = DPA.RECID

WHERE DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID

              FOR XML PATH('')), 1, LEN(','), '')  AS ADDRESSLOCATIONROLES,

DPA.LOCATIONNAME as ADDRESSDESCRIPTION,

DPA.STREET as ADDRESSSTREET,

DPA.CITY as ADDRESSCITY,

DPA.STATE as ADDRESSSTATEID,

DPA.COUNTRYREGIONID AS ADDRESSCOUNTRYREGIONID,

DPA.COUNTY as ADDRESSCOUNTYID,

DPA.DISTRICT as ADDRESSDISTRICTNAME,

DPA.ZIPCODE as ADDRESSZIPCODE,

DPA.RECID

FROM  DirPartyPostalAddressView  as DPA

JOIN VENDTABLE VT ON  DPA.PARTY = VT.PARTY

WHERE VT.DATAAREAID='USMF'

and DPA.VALIDTO='2154-12-31 23:59:59.000'