title: Understanding TSQL and COALESCE to have dynamic columns in a PIVOT
body: <p>I had asked a similar question yesterday but still don't have a basic understanding of why this logic works. I'm getting the right outupt and I'm happy with that, but why does it work the way it does?</p>
  
  <p>Say for instance we're using this simple query:</p>
  
  <pre><code>    create table #TestTable (FakeColumn varchar(50))
      INSERT INTO #TestTable (FakeColumn) VALUES ('ABC'),('DEF'),('GHI'),('JKL')
  
      DECLARE @columns VARCHAR(8000)
  
    SELECT @columns = COALESCE(@columns + ',[' + cast(FakeColumn as varchar) + ']',
    '[' + cast(FakeColumn as varchar)+ ']')
    FROM #TestTable
    GROUP BY FakeColumn
  
        select @columns
  
        drop table #TestTable
  </code></pre>
  
  <p>The output of the following query is: <code>[ABC],[DEF],[GHI],[JKL]</code> which happens to be exactly what I need... but lets say we modified the query to read:</p>
  
  <pre><code> SELECT @columns = '[' + cast(FakeColumn as varchar)+ ']'
   FROM #TestTable
   GROUP BY FakeColumn
  
   select @columns
  </code></pre>
  
  <ol>
  <li>Why is my output now: <code>[JKL]</code>? </li>
  </ol>
  
  <p>And now if we modify the <code>COALESCE</code> to only include the first argument [with the @columns appended to the front] </p>
  
  <pre><code>  SELECT @columns = @columns + ',[' + cast(FakeColumn as varchar) + ']'
          FROM #TestTable
    GROUP BY FakeColumn
  </code></pre>
  
  <ol>
  <li>Why is my output now: <code>NULL</code>?</li>
  </ol>
  
  <p>It looks as if my first value in that COALESCE statement returns NULL, therefore it should go to my second statement but that's only returning <code>[JKL]</code> ... however, with both of them combined I get the string that I need... I'm not sure how this works, or why it does. Can anyone help explain this to a rookie?</p>