How does this SQL COALESCE Function Work?

I am using some code I found on a website and I just can't get my brain around this.

   SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', 
           '[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
           FROM    (SELECT DISTINCT [DATE] FROM #DailyReport ) PV  order by [DATE]

It returns

  [02 May 2016],[03 May 2016],[04 May 2016],[05 May 2016],[06 May 2016],[08 May 2016],[09 May 2016],[10 May 2016],[11 May 2016],[12 May 2016],[13 May 2016],[15 May 2016],[16 May 2016],[17 May 2016],[18 May 2016],[19 May 2016],[20 May 2016],[22 May 2016],[23 May 2016],[24 May 2016],[25 May 2016],[26 May 2016],[27 May 2016],[29 May 2016],[30 May 2016],[31 May 2016]

but how does it work? I wish I understood how I got all those columns and why there are two '[' + CONVERT(NVARCHAR, [DATE], 106) + ']' phrases.

Solution

The two phrases you point out are simply so coalesce() works. There is a simpler way to write the logic. Perhaps this will help explain what happens:

SELECT @cols = COALESCE(@cols + ',', '') + '[' + CONVERT(NVARCHAR(255), [DATE], 106) + ']',
FROM (SELECT DISTINCT [DATE] FROM #DailyReport
     ) PV  
ORDER BY [DATE];

In your case, the full concatenation is the first argument to COALESCE(), so the expression is needed as the first argument. But, if that is NULL, then it is needed again.

Note: When use use VARCHAR(), NVARCHAR() or any character type in SQL Server, you should always include a length specifier. The default length varies by context, and if the default is not large enough, the bug can be very difficult to track down.