Pivoting a table with parametrization

Question

We have 3 tables.

tid_color - parametrization table

--------------------------
ID         ColorDescription
--------------------------
1          Green
2          Yellow
3          Red
-------------------------

tid_car - parametrization table

--------------------------
ID         CARDescription
-------------------------
1          Car X
2          Car Y
3          Car Z
-------------------------- 

table_owners_cars

------------------------------------------------
ID         CarID       ColorID      Owner
------------------------------------------------
1           1             1           John
2           1             2           Mary
3           1             3           Mary  
4           1             3           Giovanni     
5           2             2           Mary
6           3             1           Carl
7           1             1           Hawking
8           1             1           Fanny 
------------------------------------------------

CarID is FOREIGN KEY to tid_car

ColorId is FOREIGN KEY to tid_color

If we code:

SELECT tcar.CarDescription, tco.ColorDescription, Count(*) as Total
FROM table_owners_cars tocar
LEFT JOIN tid_color tco ON tco.Id = tocar.ColorId
LEFT JOIN tid_Car tcar ON tcar.Id = tocar.CarId
GROUP BY CarDescription, ColorDescription 

it results as:

Id CarDescription     ColorDescription  Total
1       CarX               Green          3
2       CarX               Yellow         1
3       CarX               Red            1
4       CarY               Yellow         1
5       CarZ               Green          1

But I want Color in the HEADER so I have code as

SELECT CarId, [1] as 'Green', [2] as 'Yellow', [3] as 'Red', [1]+[2]+[3] as 'total' 
FROM
(SELECT CarID, colorId
 FROM table_owners_cars tocar
 LEFT JOIN tid_car tc  ON tocar.CarId=tc.Id) p 
PIVOT
( 
 COUNT (ColorId)
 FOR ColorId IN ( [1], [2], [3]) 
) AS pvt

The resulting table with such SQL :

  ---------------------------------------------
   Id        Car     Green Yellow  Red    Total
   ---------------------------------------------
    1         1        3     1     1       5
    2         2        0     1     0       1                
    3         3        1     0     0       1                   
   ---------------------------------------------

It does not enable to put the description of the car (CarX, CarY, CarZ) in the Car column... instead of the first select in the previous code I have tried to put as

SELECT tc.CarDescription, [1] as 'Green', [2] as 'Yellow', [3] as 'Red', [1]+[2]+[3] as 'total'

and it throws

The multi-part identifier "tc.CarDescription" could not be bound.

I want to have the CarDescription and not its IDs as shown in the last table. The table I expect to have is the following.

I want to pivot exactly as follows:

   ---------------------------------------------
   Id        Car     Green Yellow  Red    Total
   ---------------------------------------------
    1       CarX        3     1     1       5
    2       CarY        0     1     0       1                
    3       CarZ        1     0     0       1                   
   ---------------------------------------------

How to achieve this? Thanks.

Answer 1

You can join to the Pivoted result:

 SELECT pvt.CarID, tc.Description AS Car, [1] as 'Green', [2] as 'Yellow', [3] as 'Red', [1]+[2]+[3] as 'total' 
    FROM
    (SELECT CarID, colorId
     FROM table_owners_cars tocar
     ) p 
    PIVOT
    ( 
     COUNT (ColorId)
     FOR ColorId IN ( [1], [2], [3]) 
    ) AS pvt
    INNER JOIN tid_car tc  ON pvt.CarId=tc.Id