How can I alter my dynamic String of SQL field names generated using FOR XML PATH to include aliases?

Question

I have a string variable that includes a dynamic list of column headers initiated as such:

DECLARE @Pivot VARCHAR(4000) 
SELECT @Pivot = STUFF((SELECT DISTINCT ']),MAX([' + valv.ITEM_CMDT_N + ']) AS ' + valv.ITEM_CMDT_N + ',MAX([' + valv.ITEM_CMDT_N + ' - Date' FROM [dbo].[VEND_ASGN_LKUP_VAL] valv INNER JOIN [dbo].[VEND_ASGN] va ON valv.CTRY_C = va.CTRY_C AND valv.CY_DATE_D = va.CY_DATE_D WHERE va.VEND_ASGN_I = 10 AND va.ACTV_F= 1 AND valv.ACTV_F = 1 ORDER BY ']),MAX([' + valv.ITEM_CMDT_N + ']) AS ' + valv.ITEM_CMDT_N + ',MAX([' + valv.ITEM_CMDT_N + ' - Date' FOR XML PATH('')),1,3,'') + '])'

This works great as is and results in a string like this:

MAX([TEST]) AS TEST,MAX([TEST - Date]),MAX([TEST2]) AS TEST2,MAX([TEST2 - Date])

Notice however that only the first field (ITEM_CMDT_N) included an alias. If I try to add similar code to alias the second field it looks like this:

SELECT @Pivot = STUFF((SELECT DISTINCT ']) AS [' + valv.ITEM_CMDT_N + ' - Date],MAX([' + valv.ITEM_CMDT_N + ']) AS ' + valv.ITEM_CMDT_N + ',MAX([' + valv.ITEM_CMDT_N + ' - Date' FROM [dbo].[VEND_ASGN_LKUP_VAL] valv INNER JOIN [dbo].[VEND_ASGN] va ON valv.CTRY_C = va.CTRY_C AND valv.CY_DATE_D = va.CY_DATE_D WHERE va.VEND_ASGN_I = 10 AND va.ACTV_F= 1 AND valv.ACTV_F = 1 ORDER BY ']) AS [' + valv.ITEM_CMDT_N + ' - Date],MAX([' + valv.ITEM_CMDT_N + ']) AS ' + valv.ITEM_CMDT_N + ',MAX([' + valv.ITEM_CMDT_N + ' - Date' FOR XML PATH('')),1,3,'') + '])'

and results in a string like this:

AS [TEST - Date],MAX([TEST]) AS TEST,MAX([TEST - Date]) AS [TEST2 - Date],MAX([TEST2]) AS TEST2,MAX([TEST2 - Date])

This is not obviously what I am shooting for. How can I adjust this string initialization to include aliases for the field 'valv.ITEM_CMDT_N + ' - Date' while still working with the FOR XML PATH code? Thanks!

Answer 1

SELECT @Pivot = STUFF((SELECT DISTINCT ',MAX([' + valv.ITEM_CMDT_N + ']) AS [' + valv.ITEM_CMDT_N + '],MAX([' + valv.ITEM_CMDT_N + ' - Date]) AS [' + valv.ITEM_CMDT_N + ' - Date]' FROM [dbo].[VEND_ASGN_LKUP_VAL] valv INNER JOIN [dbo].[VEND_ASGN] va ON valv.CTRY_C = va.CTRY_C AND valv.CY_DATE_D = va.CY_DATE_D WHERE va.VEND_ASGN_I = 10 AND va.ACTV_F= 1 AND valv.ACTV_F = 1 ORDER BY ',MAX([' + valv.ITEM_CMDT_N + ']) AS [' + valv.ITEM_CMDT_N + '],MAX([' + valv.ITEM_CMDT_N + ' - Date]) AS [' + valv.ITEM_CMDT_N + ' - Date]' FOR XML PATH('')),1,1,'')