Using "PIVOT" in SQL

Question

I made this question a while back.

SQL Server making rows into columns

Basically, the person that answered explained very well what I needed to do. However, I encountered a problem

aID| status | group   |
-----------------------
1  |   acti |  group1 |
2  |   inac |  group2 |
A3  |   acti |  group1 |

Second Table: This table is fixed. It has around 20 values and the IDs are all numbers

atID| traitname  |
------------------
 1  |   trait1   |
 2  |   trait2   |
 3  |   trait3   |

Third Table: This table is used to identify the traits the assets in the first table have. The fields that have the same name as fields in the above tables are obviously linked.

tID|   aID  |   atID |   trait   |
----------------------------------
1  |   1    |    1   |   NAME    |
2  |   1    |    2   |   INFO    |
3  |   2    |    3   |   GOES    |
4  |   2    |    1   |   HERE    |

Now, the user wants the program to output the data in the following format:

aID| status | group  | trait1 | trait2 | trait 3
-------------------------------------------------
1  |  acti  |  group1 |  NAME |  INFO  | NULL
2  |  inac  |  group2 |  HERE |  NULL  | GOES
A3 |  acti  |  group1 |  NULL |  NULL  | NULL

Now, the problem here is that, as you can see, A3 has no trait. In the final view, I would want A3 to appear completely null in the traits. But it doesn't appear at all, even though it's there. Does anyone know how can I fix this?

Here is the query I am using:

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

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

set @query = 'SELECT aid, status, [group],' + @cols + ' 
            from 
            (
              select t1.aid,
                t1.status,
                t1.[group],
                t2.traitname,
                t3.trait
              from table1 t1
              inner join table3 t3
                on t1.aid = t3.aid
              inner join table2 t2
                on t3.atid = t2.atid
            ) x
            pivot 
            (
                max(trait)
                for traitname in (' + @cols + ')
            ) p '

execute sp_executesql @query;

Answer 1

The problem is you are using an INNER JOIN on your tables. An INNER JOIN returns all rows that have matching data in both tables.

Since you want to return everything from Table1, you will alter your query to use a LEFT JOIN instead of the INNER JOIN:

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

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

set @query = 'SELECT aid, status, [group],' + @cols + ' 
            from 
            (
              select t1.aid,
                t1.status,
                t1.[group],
                t2.traitname,
                t3.trait
              from table1 t1
              left join table3 t3
                on t1.aid = t3.aid
              left join table2 t2
                on t3.atid = t2.atid
            ) x
            pivot 
            (
                max(trait)
                for traitname in (' + @cols + ')
            ) p '

execute sp_executesql @query;

As a side note, it is difficult to tell what the data types on your join columns are. Your Table1.aid column appears to be a varchar because of the A3 value but your Table3.aid column looks to be an int. If your data types are not the same, then you will need to cast the data on the join similar to -- on t1.aid = cast(t3.aid as varchar(10))