SQL Server dynamic pivot returning null value when none exist in the data

Question

I have 3 tables (tblPreference, tblCustomer, tblCustomerPreference) that look something like the following:

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

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:

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

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 NULL for any PreferenceID's that are populated for other customers. If I run the query without the PIVOT expression it returns all Preferences populated as expected. It's only when I PIVOT the data does the NULL creep in. I'm hoping I missed something simple, but I'm not seeing the error.

Answer 1

The only reason you're even seeing preference3 default values in CustomerID's 1&2 is because there is NO tblCustomerPreference record for preference3, not because there isn't a tblCustomerPreference record for the combination of CustomerID=1 / Preference3 and CustomerID=2 / Preference3.

In your RIGHT JOIN condition, you are specifying to only join between tblCustomerPreference and tblPreference on the preference value only - this will only ever materialize a record from tblPreference that has NO matching record for ANY customerID in tblCustomerPreference. If you add an additional join condition on customerID = @innerCustomerID for that clause, you'll now be doing what you are looking for: i.e. give me ALL preference records and ANY matching tblCustomerPreference for CustomerID=@innerCustomerID.

Try it by simply adding in a tblCustomerPreference record for CustomerID 1 and Preference3, you'll notice that you'll start seeing not only NULL for the Prefernce3 value for Customer2, but you'll no longer even get a result for Customer 3.

Looks like this is what you were trying to do in your WHERE clause, but since JOINs are processed before the WHERE clause during query processing (following the proper logical ordering of statement processing), you're getting an intermediate resultset that is strictly based on preference combination as opposed to customer AND preference combination.

So, a couple of small changes and you should be good. Basically just add an additional condition to your RIGHT JOIN clause specifying a specific customer, i.e. @innerCustomerID and remove your entire WHERE clause and you're all set. Note that this will also have the side-effect of actually returning all default values for any @CustomerID passed that doesn't even exist as a customer - if you want to change that to return nothing for non-existent customers, simply add a check prior to the query or include a where exists() filter:

alter 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
    AND tblCustomerPreference.CustomerID = @innerCustomerID
   ) data
   PIVOT (MAX(Value)
       FOR PreferenceName IN (' + @PivotColumns + ')) PreferencePivot'

 print @sqlText

 EXECUTE sp_executesql @sqlText, N'@innerCustomerID int', @CustomerID
END