使用参数化旋转表

Question

我们有3个表。

tid_color - 参数表

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

tid_car - 参数表

--------------------------
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是tid_car的外键

ColorId 是tid_color的外键

如果我们编码：

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 

其结果为：

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

但是我想在标题中显示颜色，所以我有代码

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

结果表与这样的 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                   
   ---------------------------------------------

它无法将汽车的描述(CarX，CarY，CarZ(放在汽车列中...而不是我试图放入的先前代码中的第一个选择

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

它抛出

多部分标识符"tc.汽车描述"无法绑定。

我想要的是CarDescription，而不是上表中所示的ID。我期望有的表格如下。

我想完全按照如下方式进行透视：

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

如何实现这一点？谢谢。

Answer 1

您可以加入透视结果：

 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