title: How to create a pivot table when column is sometimes NULL
body: <p>I have created a t-sql that I can use via command line that builds an HTML page off of my tables.  The problem is that I cannot set the ANSI WARNING to OFF because of the following error:</p>
  
  <pre><code>     Verify that SET options are correct for use with indexed views and/or 
       indexes on computed columns and/or filtered indexes and/or query 
       notifications and/or XML data type methods and/or spatial index operations.
  </code></pre>
  
  <p>I have not been able to traced where the NULL is coming from and it is the query that generates my pivot table.   Below is my code and some test data.</p>
  
  <pre><code>     DECLARE @ECR_Drugs  TABLE
         (FormID varchar(max),
         Subject_Id varchar(7),
         ExamDate varchar(max),
         Drug_name varchar(max),
         Currently_taking varchar(max),
         StartDate date,
         StartDate_Unknown varchar(max),
         EndDate varchar(max),
         EndDate_Unknown varchar(max))
  
       INSERT INTO @ECR_DRUGS VALUES('Enrollment_ChartReview', '42-0085', 'Sep 05, 2012', 'Ethambutol', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('Enrollment_ChartReview', '42-0085', 'Sep 05, 2012', 'Isoniazid', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('Enrollment_ChartReview', '42-0085', 'Sep 05, 2012', 'Pyrazinamide', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('Enrollment_ChartReview', '42-0085', 'Sep 05, 2012', 'Rifampicin', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('Enrollment_ChartReview', '42-0085', 'Sep 05, 2012', 'Streptomycin', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('30Day_ChartReview', '42-0085', 'Oct 04, 2012', 'Ethambutol', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('30Day_ChartReview', '42-0085', 'Oct 04, 2012', 'Isoniazid', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('30Day_ChartReview', '42-0085', 'Oct 04, 2012', 'Pyrazinamide', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('30Day_ChartReview', '42-0085', 'Oct 04, 2012', 'Rifampicin', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('30Day_ChartReview', '42-0085', 'Oct 04, 2012', 'Streptomycin', 'Yes', '2012-05-12', 'False', 'Jan 01, 1900', 'False')
       INSERT INTO @ECR_DRUGS VALUES('FUP_ChartReview', '42-0085', 'Sep 05, 2013', 'Ethambutol', 'No', '2012-05-07', 'False', 'Jan 01, 1900', 'True')
       INSERT INTO @ECR_DRUGS VALUES('FUP_ChartReview', '42-0085', 'Sep 05, 2013', 'Isoniazid', 'No', '2012-05-07', 'False', 'Jan 01, 1900', 'True')
       INSERT INTO @ECR_DRUGS VALUES('FUP_ChartReview', '42-0085', 'Sep 05, 2013', 'Pyrazinamide', 'No', '2012-05-07', 'False', 'Jan 01, 1900', 'True')
       INSERT INTO @ECR_DRUGS VALUES('FUP_ChartReview', '42-0085', 'Sep 05, 2013', 'Rifampicin', 'No', '2012-05-07', 'False', 'Jan 01, 1900', 'True')
       INSERT INTO @ECR_DRUGS VALUES('FUP_ChartReview', '42-0085', 'Sep 05, 2013', 'Streptomycin', 'No', '2012-05-07', 'False', 'Jul 09, 2012', 'False')
  
  
       SELECT
         ISNULL([sub].[Drug_Name],NULL),
         MAX(CASE WHEN [Enrollment_ChartReview] IS NOT NULL THEN Currently_taking ELSE NULL END) AS Enroll_CurrentlyTaking
       FROM
          (SELECT * 
             FROM  @ECR_Drugs
             pivot (max (StartDate) FOR FormID in ([Enrollment_ChartReview], [30Day_ChartReview], [FUP_ChartReview])) AS pvt
            ) AS [sub]
       GROUP BY
         [sub].[Drug_Name]
  </code></pre>
  
  <p>Is there a t-sql solution ?</p>