I need help identifying syntax error with T-SQL string agg function using convert varchar(max)-CodePudding

I'm attempting the string_agg function with convert(varchar(max)) because the results exceededs the 8000 characters limit, but now I am getting a syntax error under

WITHIN GROUP (ORDER BY PURP.PROGRAM_YEAR DESC) AS 'GS SUMMARY'

Here is what I have:

SELECT DISTINCT
    tbl1.ID_NUMBER,
    STRING_AGG(CONVERT(VARCHAR(MAX), 'PROPOSAL# ', pro.PROPOSAL_ID,': ', purp.PROGRAM_YEAR,', ', tpro.SHORT_DESC, ', ', tsub.SHORT_DESC, ', ASK: ',
                FORMAT(pro.ORIGINAL_ASK_AMT,'C0','en-us'),', ANTIC: ', 
                FORMAT(pro.ANTICIPATED_AMT,'C0','en-us'), ', UNIT: ', 
                pro.UNIT_CODE, ', SUBMITTER: ',
                purp.SUBMITTER, ', BENEFIT UNIT: ', TFUND.SHORT_DESC), ';') WITHIN GROUP (ORDER BY PURP.PROGRAM_YEAR DESC) AS 'SUMMARY' 
FROM
    TABLE1 tbl1

CodePudding user response：

Try with a CONCAT

Then CAST or CONVERT the concatinated string to a VARCHAR(MAX)

, STRING_AGG(CAST(CONCAT(
  'PROPOSAL# ', pro.PROPOSAL_ID,
  ': ', purp.PROGRAM_YEAR,
  ', ', tpro.SHORT_DESC, 
  ', ', tsub.SHORT_DESC, 
  ', ASK: ', FORMAT(pro.ORIGINAL_ASK_AMT,'C0','en-us'), 
  ', ANTIC: ', FORMAT(pro.ANTICIPATED_AMT,'C0','en-us'), 
  ', UNIT: ', pro.UNIT_CODE, 
  ', SUBMITTER: ', purp.SUBMITTER, 
  ', BENEFIT UNIT: ', TFUND.SHORT_DESC
  ) AS VARCHAR(MAX)), ';') WITHIN GROUP (ORDER BY PURP.PROGRAM_YEAR DESC) AS SUMMARY

A CONVERT doesn't expect that many parameters.

CodePudding user response：

In case anyone else runs into this I found the answer. I was able to remedy it by adding the concat function:

, STRING_AGG(CONVERT(VARCHAR(max), CONCAT('PROPOSAL# ', pro.PROPOSAL_ID,': ', purp.PROGRAM_YEAR,', ', tpro.SHORT_DESC, ', ', tsub.SHORT_DESC, ', ASK: '
                , FORMAT(pro.ORIGINAL_ASK_AMT,'C0','en-us'),', ANTIC: ', FORMAT(pro.ANTICIPATED_AMT,'C0','en-us'), ', UNIT: ', pro.UNIT_CODE, ', SUBMITTER: '
                , purp.SUBMITTER, ', BENEFIT UNIT: ', TFUND.SHORT_DESC)), '; ') 
                WITHIN GROUP (ORDER BY PURP.PROGRAM_YEAR DESC) AS 'SUMMARY'