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

Question

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

LukStorms · Accepted Answer · 2022-01-28T16:32:58.333

0

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.

edited Jan 28 '22 at 16:32

answered Jan 28 '22 at 15:49

LukStorms

28,916
5
31
45

@lptr Good point. Casting the concatination should work for this case. – LukStorms Jan 28 '22 at 16:36

score 0 · Answer 2 · answered Jan 28 '22 at 17:42

0

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'

answered Jan 28 '22 at 17:42

Kendra

11
3

Thanks so much LukStorms! Your suggestion was the ticket! – Kendra Jan 28 '22 at 17:43
1

The proper way to use this site is to mark the response that provided you with the answer (or path to it) and not post your own version of it. FWIW - learn to format your code so that it is easily read - that will reduce the chances of mistakes and misunderstanding. Another best practice is to avoid using string constants as alias name. Note that CAST and CONVERT to the same thing here. – SMor Jan 28 '22 at 18:17

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

2 Answers2