title: SQL Server dynamic pivot returning null value when none exist in the data
body: <p>I have 3 tables (<code>tblPreference</code>, <code>tblCustomer</code>, <code>tblCustomerPreference</code>) that look something like the following:</p>
  
  <pre><code>tblPreference:
  ID       | Name            | DefaultValue
  (int PK) | (nvarchar(100)) | (nvarchar(100))
  -------------------------------
  1        | Preference1     | 1
  2        | Preference2     | Yes
  3        | Preference3     | 1
  
  tblCustomer:
  CustomerID | ...
  (int PK)
  --------------------
  1          | ...
  2          | ...
  3          | ...
  
  tblCustomerPreference:
  ID       | CustomerID | PreferenceID | Value
  (int PK) | (int)      | (int)        | (nvarchar(100))
  -------------------------------------------------------
  1        | 1          | 1            | 0
  2        | 1          | 2            | Yes
  3        | 2          | 1            | 0
  4        | 2          | 2            | No
  </code></pre>
  
  <p>I'm creating a pivot of this data so it's all in a single row using the following stored procedure so that it will always pull back all preferences and if it finds a Customer specific value it will return that otherwise it returns the default value:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_GetCustomerPreferences] @CustomerID int AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @PivotColumns nvarchar(max)
      DECLARE @PivotColumnsSelectable nvarchar(max)
      SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(Preference.Name),
             @PivotColumnsSelectable = COALESCE(@PivotColumnsSelectable + ',' + Char(10),'') + Preference.Source + '.' + QUOTENAME(Preference.Name) + ' AS ' + QUOTENAME(Preference.Name)
      FROM (SELECT [Name],
                   'PreferencePivot' AS [Source]
            FROM [dbo].[tblPreference]) Preference
  
      DECLARE @sqlText nvarchar(max)
      SELECT @sqlText = 'SELECT ' + @PivotColumnsSelectable + '
      FROM (SELECT tblPreference.Name AS PreferenceName,
                  CASE
                      WHEN tblCustomerPreference.Value IS NOT NULL THEN tblCustomerPreference.Value
                      ELSE tblPreference.DefaultValue
                  END AS Value,
                  @innerCustomerID AS CustomerID
              FROM tblCustomerPreference
                  RIGHT JOIN tblPreference ON tblCustomerPreference.PreferenceID = tblPreference.ID
              WHERE (tblCustomerPreference.CustomerID = @innerCustomerID OR tblCustomerPreference.ID IS NULL)) data
              PIVOT (MAX(Value)
                     FOR PreferenceName IN (' + @PivotColumns + ')) PreferencePivot'
  
      EXECUTE sp_executesql @sqlText, N'@innerCustomerID int', @CustomerID
  END
  </code></pre>
  
  <p>The issue I'm running into is that when I query for CustomerID 1 or 2, everything comes back as expected with all values populated as expected.  But if I query for CustomerID 3, it will return a <code>NULL</code> for any PreferenceID's that are populated for other customers.  If I run the query without the <code>PIVOT</code> expression it returns all Preferences populated as expected.  It's only when I PIVOT the data does the <code>NULL</code> creep in.  I'm hoping I missed something simple, but I'm not seeing the error.</p>