question

EagleFire26-2202 avatar image
0 Votes"
EagleFire26-2202 asked MelissaMa-msft edited

SQL Server += string Concatenation not working

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks this helps, the return values would need to also be Distinct however this throws an error with the order by clause , unfortunately for me String_AGG is not an available function.

0 Votes 0 ·

What result did you get? Does it contain duplicates?


0 Votes 0 ·

When I try using distinct with the output I get an error:
Order By items must appear in the select list if SELECT DISTINCT is specified

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @EagleFire26-2202,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks this is throwing a Order By items must appear in the select list if SELECT DISTINCT is specified error

0 Votes 0 ·

Hi @EagleFire26-2202,

You could try with order by 1 to replace your order by part.

I tried with it in my own environment and it did not report any error.

If there are two or more columns in your order by part, you could try with order by 1,2 or order by 1,2,3 etc.

If above is still not working, you could provide your whole updated code and we would check further.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.