title: Dynamic SQL Pivot Causing Duplicate Columns
body: <p>I created a Dynamic SQL query which joins a few tables, and then pivots off a table called Geometries which stores Name/Value pairs.  The SQL is dynamic, because at run-time I won't know which name/value pairs are required to pivot off of.  </p>
  
  <p>The query below gets me the data I need, however, all of the geometry columns are listed twice as if I pivoted twice off the same data.  I cannot figure out why this happening.  I suspect it has to do with the fact that I select the Name/Value pairs in my common table expression which selects the Geometry Name/Value columns on line 13 of the code sample below: <code>g.Name[Geometry], g.Value</code></p>
  
  <p>Maybe coupling that with the PIVOT code near the bottom is causing the duplicate column data in my query results?</p>
  
  <p>I can't remove the code on line 13, or the PIVOT fails with errors saying Geometry and Value are not valid column names.</p>
  
  <p>Anyway, here is the query...</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
  SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(g.Name) 
                      FROM Geometries g
                      FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)') 
                  ,1,1,'')
  SET @query = 
      'SELECT *, ' + @cols + ' 
      FROM (
              SELECT 
                  r.Id[RunId], rd.Id[RunDataId], r.RunNumber [Run #], r.TestNumber [Test #], r.Description,
                  rd.data1, rd.data2, rd.data3,
                  g.Name[Geometry], g.Value
              FROM dbo.Runs r
                  INNER JOIN RunDatas rd ON r.Id = rd.RunId
                  INNER JOIN RunGeometries rg ON rg.RunId = r.Id
                  INNER JOIN Geometries g ON g.Id = rg.GeometryId
          ) as data
              PIVOT
              (
                  Max(Value) FOR Geometry IN (' + @cols + ')
              ) as p'
  
  execute sp_executesql @query
  </code></pre>
  
  <p>Any help figuring out what is causing the duplicate data would be appreciated.</p>