SQL Pivot table returning NULL for non-existent child table values

I have a typical RDMS setup where records in a main table can have optional records in a related table via a M2M join. I'm trying to PIVOT this data but in cases where there is no relation I want to return a default value. The join I have below is returning NULL.

select * 
from
(
    SELECT s.Biz_Name, la.Name AS Association, ISNULL(i.Location, 'Default') as Location
    FROM  dbo.ShopAssociations sa 
    INNER JOIN dbo.LookupAssociations la 
        ON sa.AssociationID = la.AssociationID 
    RIGHT JOIN dbo.Basic_Shop_Info s 
        ON sa.ShopID = s.ShopID 
    INNER JOIN dbo.Images i 
        ON la.ImageID = i.ImageID
) DataTable
PIVOT 
(
    min(Location) 
    for associ开发者_如何学JAVAation in
        ([OnCall],[OCGuy],[ASCLogo],[ASC_OtherSt],[ASE],[AASP],[AASP_PA],
        [ASE_BlueSeal],[AAA],[AAA-B],[ASA],[ATRA],[ICAR],[CAA],[ACDelco],
        [Cert],[ASC],[BBB],[Goodyear],[Limos],[RVs],[Bosch],[NARSA],
        [DiscTire],[BigO],[Tires],[Firestone],[ASCCA],[JustTires],[ASE_Blue])
) PivotTable

The output looks like this:

BizName          OnCall     OCGuy     ASCLogo ASC_OtherSt ASE ...
"Wonderful Biz"  somevalue  somevalue NULL    somevalue   NULL

What I am trying to achieve is if a child record doesn't exist in INNER JOIN from Basic_Shop_Info to ShopAssociations that we get "Default" instead of NULL. I've tried ISNULL(), Coalesce() and even a CASE statement, all with the same results.

Based on your comment it sounds like you found a solution. I am only answering this to provide a suggestion based on the fact you are pivoting so many columns and they are all hard-coded. You can use dynamic SQL for a PIVOT and your query would look something like this:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @colsPivot AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from dbo.LookupAssociations
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsPivot = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(Name) +', ''Default'')'
                    from dbo.LookupAssociations
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT Bizname, ' + @colsPivot + ' from 
            (
                SELECT s.Biz_Name, la.Name AS Association, ISNULL(i.Location, ''Default'') as Location
                FROM  dbo.ShopAssociations sa 
                INNER JOIN dbo.LookupAssociations la 
                    ON sa.AssociationID = la.AssociationID 
                RIGHT JOIN dbo.Basic_Shop_Info s 
                    ON sa.ShopID = s.ShopID 
                INNER JOIN dbo.Images i 
                    ON la.ImageID = i.ImageID
            ) x
            pivot 
            (
                min(Location)
                for association in (' + @cols + ')
            ) p 
            '

execute(@query)

The value @colsPivot is adding the IsNull() around each of you columns so you can put in place the Default value. But this should provide the same result as your original query where everything was hard-coded.

This will get the list of columns at run-time so then you do not have to hard-code anything and it will accept new values without having to change the query.

I got this:

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

    select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by person_nbr 
                                               order by person_nbr,first_name,        last_name, medication_name) rn
                      from TA_PIVOT
                    ) d
                    cross apply
                    (
                      select 'diag' col, 1 sort
                    ) c
                    group by col, rn, sort
                    order by rn, sort
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = 'SELECT person_nbr, first_name, last_name,medication_name,' + @cols + '
              from
              (
                select person_nbr,first_name,last_name,medication_name,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                 -- when you perform an unpivot the datatypes have to be the same. 
                 -- you might have to cast the datatypes in this query
                  select person_nbr,first_name,last_name, medication_name,   cast(icd_code_id as varchar(500)) diag,
                    row_number() over(partition by person_nbr order by person_nbr,   first_name, last_name,medication_name) rn
                  from ta_pivot
                ) src
                unpivot
                (
                  value
                  for col in (diag)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in (' + @cols + ')
              ) p '

    execute(@query);