SQL Server += string Concatenation not working

Question

Hi,

I am trying to setup the foundations of a dynamic pivot table query.

With the following

[CODE}
DECLARE @STARTDATE AS DATETIME
DECLARE @ENDDATE AS DATETIME
DECLARE @PIVOTECOLUMN AS NVARCHAR(MAX) = ''
DECLARE SQLQUERY AS VARCHAR (MAX) = ''

SET @STARTDATE = DATEADD(M, DATEDIFF(M, 0, DATEADD(MONTH, -36, GETDATE())), 0)
SET @ENDDATE = DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0 )

SELECT DISTINCT @PIVOTCOLUMN += QUOTENAME(FORMAT(CF.STATEMENT_START_DATE, 'MM-yyyy')) + ','

FROM TABLE1 CF
INNER JOIN TABLE 2 CH CF.KEY1 = CH.KEY2
WHERE CH.RECEIPT_DATE BETWEEN @STARTDATE AND @ENDDATE
AND @ENDDATE BEWEEN @STARTDATE AND @ENDDATE

SET @PIVOTCOLUMN = LEFT(@PIVOTCOLUMN, LEN(@PIVOATCOLUMN) -1)
SELECT @PIVOTCOLUMN
`
[/CODE]
the main query without the += returns 36 previous months correctly in a single column.

05-2018
06-2019
07-2020
01-2020
02-2020

however with the += when I try to run select @pivotcolumn or print to see what is in @pivotcolumn
only 1 month displays like the following

01-2018

instead of

05-2018, 06-2019, 07-2020, 01-2020, 02-2020

I was wondering what I was doing wrong here and how to also get the date column output to be ascending order from oldest date to newest date

thank you

Answer 1

Try an alternative:

SELECT @PIVOTCOLUMN += QUOTENAME(FORMAT(CF.STATEMENT_START_DATE, 'MM-yyyy')) + ','
FROM TABLE1 CF
INNER JOIN TABLE2 CH CF.KEY1 = CH.KEY2
WHERE CH.RECEIPT_DATE BETWEEN @STARTDATE AND @ENDDATE
AND @ENDDATE BEWEEN @STARTDATE AND @ENDDATE
group by CF.STATEMENT_START_DATE
order by CF.STATEMENT_START_DATE

Also consider STRING_AGG, if available.

Answer 2

SELECT DISTINCT @PIVOTCOLUMN += QUOTENAME(FORMAT(CF.STATEMENT_START_DATE, 'MM-yyyy')) + ','

The use of += this way has no defined correct behaviour. You get what you get. Or in other words, don't use it.

Use string_agg if you are on SQL 2017 or later. Else use FOR XML PATH('').

I have a primer on dynamic pivot here: https://www.sommarskog.se/dynamic_sql.html#pivot. This section includes examples with both string_agg and FOR XML PATH.

Answer 3

Hi @EagleFire26 ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working.

SELECT  @PIVOTCOLUMN = STUFF((   
SELECT DISTINCT ',' + QUOTENAME(FORMAT(CF.STATEMENT_START_DATE, 'MM-yyyy'))  
FROM TABLE1 CF  
INNER JOIN TABLE2 CH ON  CF.KEY1 = CH.KEY2  
WHERE CH.RECEIPT_DATE BETWEEN @STARTDATE AND @ENDDATE  
AND @ENDDATE BETWEEN @STARTDATE AND @ENDDATE  
ORDER BY 1  
FOR XML PATH('') ), 1, 1, '')   
  
SELECT @PIVOTCOLUMN  

You could also use below instead of 'ORDER BY 1'.

ORDER BY ',' + QUOTENAME(FORMAT(CF.STATEMENT_START_DATE, 'MM-yyyy'))  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

To avoid the ORDER BY / DISTINCT clash, form your query with a CTE:

; WITH CTE AS (
    SELECT DISTINCT mycol ....
    FROM  ...
)
SELECT @pivotcolumnn = 
   (SELECT quotenmae(mycol) + ','
    FROM   CTE
    ORDER BY mycol
    FOR XML PATH(''), TYPE).value('.', nvarchar(MAX))

SELECT @pivotcolumn = substring(@pivotcolumn, 1, len(@pivotcolumn) - 1)