title: Dynamic SQL Pivot Query with Union All
body: <p>I have a static pivot query that tracks historical pricing (see below); it is union all of one table of current data, one table of historical data, and one related table. </p>
  
  <p>I would like to convert it to a dynamic SQL pivot query so that I don't manually have to update the column heading dates all of the time, but I am having problems doing so. The data comes out of an accounting system (Microsoft Dynamics) running on SQL Server, and due to system limitations, I am unable to use ansi nulls/ paddings/ warnings and also unable to use quoted identifiers. Is there another way to create a dynamic sql pivot query?</p>
  
  <p>Here is my current query. I want to replace the month-end dates with dynamic sql. I have view access to the data only.</p>
  
  <pre><code>SELECT ACTDESCR AS "ACCT NAME", concat(right(actnumbr_2,2), actnumbr_3) as PLU, [3/31/2015], [2/28/2015], [1/31/2015], [12/31/2015], [11/30/2014], [10/31/2014], [9/30/2014], [8/31/2014], [7/31/2014], [6/30/2014], [5/31/2014], [4/30/2014], [3/31/2014], [2/28/2014], [1/31/2014]
  FROM
  (SELECT A.ACTDESCR, EOMONTH(DATEFROMPARTS(B.YEAR1,B.PERIODID,1),0) AS DATE1, A.actnumbr_2, a.actnumbr_3, B.PERDBLNC, A.ACTNUMBR_1
  FROM TEST.dbo.table1 AS A   
  
  LEFT OUTER JOIN TEST.dbo.TABLE2 AS b
  ON (a.ACTINDX = b.ACTINDX)
  
  UNION ALL
  
  SELECT A.ACTDESCR, EOMONTH(DATEFROMPARTS(C.YEAR1,C.PERIODID,1),0) AS DATE1, A.actnumbr_2, a.actnumbr_3, C.PERDBLNC, A.ACTNUMBR_1
  FROM TEST.dbo.TABLE1 AS A
  
  LEFT OUTER JOIN TEST.dbo.TABLE3 AS C
  ON (a.ACTINDX = C.ACTINDX)
  ) AS ST
  
  PIVOT
  (
  SUM(PERDBLNC)
  FOR DATE1 IN ([3/31/2015], [2/28/2015], [1/31/2015], [12/31/2015], [11/30/2014], [10/31/2014], [9/30/2014], [8/31/2014], [7/31/2014], [6/30/2014], [5/31/2014], [4/30/2014], [3/31/2014], [2/28/2014], [1/31/2014])
  
  ) AS PVT
  
  WHERE ACTNUMBR_1 = ?
  </code></pre>