title: SQL pivot table cross apply over counting results
body: <p>I'm using a pivot table with dynamically created columns to create a report that counts actions by month. Within the dynamic query is a CROSS APPLY involving three columns. This is causing my totals to be three times greater then they should be. I'm currently hacking a solution where I divide the count by three to get the correct answer. Can anyone help me come up with a more elegant solution to this problem?</p>
  
  <p>EDIT: I'm using SQL Server 2008R2</p>
  
  <p>Given this dataset (it is actually a view but I didn't feel it was wise to recreate the entire schema in SO):</p>
  
  <pre><code>CREATE TABLE vw_ActionsReport
      ([CID] int, [MitigationActionID] int, [Approved] int, [Status] varchar(11), [ChangedDate] datetime, [EntryDate] varchar(7), [STATE_ABBR] varchar(2), [STATE_NAME] varchar(11), [CENSUS_NAM] varchar(12), [CIS_NAME] varchar(21), [COUNTY_NAM] varchar(9), [CO_FIPS] int, [REGION] int, [ST_FIPS] int);
  
  INSERT INTO vw_ActionsReport
      ([CID], [MitigationActionID], [Approved], [Status], [ChangedDate], [EntryDate], [STATE_ABBR], [STATE_NAME], [CENSUS_NAM], [CIS_NAME], [COUNTY_NAM], [CO_FIPS], [REGION], [ST_FIPS])
  VALUES
      (090069, 5475, 1, 'Identified', '2012-11-27 16:21:27', '11_2012', 'CT', 'CONNECTICUT', 'OLD SAYBROOK', 'OLD SAYBROOK, TOWN OF', 'MIDDLESEX', 09007, 01, 09),
      (090069, 5476, 1, 'In Progress', '2012-11-27 16:21:27', '11_2012', 'CT', 'CONNECTICUT', 'OLD SAYBROOK', 'OLD SAYBROOK, TOWN OF', 'MIDDLESEX', 09007, 01, 09),
      (090012, 6687, 1, 'Identified', '2013-04-02 16:14:03', '4_2013', 'CT', 'CONNECTICUT', 'NORWALK', 'NORWALK, CITY OF', 'FAIRFIELD', 09001, 01, 09),
      (090008, 6993, 1, 'Identified', '2013-06-20 15:18:38', '6_2013', 'CT', 'CONNECTICUT', 'GREENWICH', 'GREENWICH, TOWN OF', 'FAIRFIELD', 09001, 01, 09),
      (090019, 17000, 0, 'Identified', '2013-11-26 11:46:14', '11_2013', 'CT', 'CONNECTICUT', 'WESTPORT', 'WESTPORT, TOWN OF', 'FAIRFIELD', 09001, 01, 09);
  </code></pre>
  
  <p>Then, using the following query:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
          @cols_math AS NVARCHAR(MAX),
          @query AS NVARCHAR(MAX);
  
  SELECT @cols = STUFF
          (
            (
              SELECT ',' + QUOTENAME(EntryDate)
              FROM dbo.vw_ActionsReport
              GROUP BY EntryDate,
                       DATEPART(YEAR, dbo.vw_ActionsReport.ChangedDate),
                       DATEPART(MONTH, dbo.vw_ActionsReport.ChangedDate)
              ORDER BY DATEPART(YEAR, dbo.vw_ActionsReport.ChangedDate),
                       DATEPART(MONTH, dbo.vw_ActionsReport.ChangedDate)
              FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,1,''
          );
  
  SELECT @cols_math = STUFF
          (
            (
              -- This is my hack where I divide the answer by three
              SELECT ',' + QUOTENAME(EntryDate) + ' / 3 AS ' + QUOTENAME(EntryDate)
              FROM dbo.vw_ActionsReport
              GROUP BY EntryDate,
                       DATEPART(YEAR, dbo.vw_ActionsReport.ChangedDate),
                       DATEPART(MONTH, dbo.vw_ActionsReport.ChangedDate)
              ORDER BY DATEPART(YEAR, dbo.vw_ActionsReport.ChangedDate),
                       DATEPART(MONTH, dbo.vw_ActionsReport.ChangedDate)
              FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1,1,''
          );
  
  SET @query = 'SELECT REGION, STATE_ABBR, [Status],' + @cols_math + ' FROM
               (
                  SELECT REGION, STATE_ABBR, [Status], EntryDate, MitigationActionID
                  FROM dbo.vw_ActionsReport
                  CROSS APPLY
                  (
                    SELECT CAST(REGION AS VARCHAR(50)) UNION ALL
                    SELECT CAST(STATE_ABBR AS VARCHAR(50)) UNION ALL
                    SELECT CAST([Status] AS VARCHAR(50))
                  ) c(col)
               ) x
               PIVOT
               (
                  COUNT(MitigationActionID)
                  FOR EntryDate IN (' + @cols + ')
               ) p ';
  
  EXECUTE(@query);
  </code></pre>
  
  <p>The output should look like this:</p>
  
  <pre><code>+-REGION-+-STATE_ABBR-+-Status------+-11_2012-+-4_2013-+-6_2013-+-11_2013-+
  | 01     | CT         | Identified  | 1       | 1      | 1      | 1       |
  | 01     | CT         | In Progress | 1       | 0      | 0      | 0       |
  </code></pre>
  
  <p>If I don't include my hack as show in the comment in the query above all of the 1's in the output table become 3's. I'm pretty sure it's coming from the CROSS APPLY, because if I change the number of SELECTS in the CROSS APPLY the totals change the same way.</p>
  
  <p>I'm sure there is a better way to do this but I've been unsuccessful. I spent quite a bit of time trying to get DISTINCT to work but I couldn't seem to get COUNT DISTINCT to work from within the PIVOT.</p>
  
  <p>If anyone can give me any advice on this, I would greatly appreciate it.</p>