title: How can I alter my dynamic String of SQL field names generated using FOR XML PATH to include aliases?
body: <p>I have a string variable that includes a dynamic list of column headers initiated as such:</p>
  
  <pre><code>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,'') + '])'
  </code></pre>
  
  <p>This works great as is and results in a string like this:</p>
  
  <pre><code>MAX([TEST]) AS TEST,MAX([TEST - Date]),MAX([TEST2]) AS TEST2,MAX([TEST2 - Date])
  </code></pre>
  
  <p>Notice however that only the first field (<code>ITEM_CMDT_N</code>) included an alias. If I try to add similar code to alias the second field it looks like this:</p>
  
  <pre><code>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,'') + '])'
  </code></pre>
  
  <p>and results in a string like this:</p>
  
  <pre><code>AS [TEST - Date],MAX([TEST]) AS TEST,MAX([TEST - Date]) AS [TEST2 - Date],MAX([TEST2]) AS TEST2,MAX([TEST2 - Date])
  </code></pre>
  
  <p>This is not obviously what I am shooting for. How can I adjust this string initialization to include aliases for the field <code>'valv.ITEM_CMDT_N + ' - Date'</code> while still working with the FOR XML PATH code? Thanks!</p>