用参数化旋转表 [英] Pivoting a table with parametrization

问题描述

我们有3张桌子.

tid_color-参数化表

tid_color - parametrization table

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

tid_car-参数化表

tid_car - parametrization table

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

table_owners_cars

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

CarID is FOREIGN KEY to tid_car

ColorId是tid_color的外键

ColorId is FOREIGN KEY to 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

但是我想要HEADER中的Color，所以我的代码为

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

具有这样的SQL的结果表:

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                   
   ---------------------------------------------

它无法将汽车的描述(CarX，CarY，CarZ)放入汽车"列中...而不是我尝试放置为

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'

它会抛出

无法绑定多部分标识符"tc.CarDescription".

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

我想要的是 CarDescription ，而不是上表所示的ID.我希望拥有的表如下.

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.

我要严格按照以下说明进行操作:

   ---------------------------------------------
   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.

推荐答案

您可以加入透视"结果:

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

这篇关于用参数化旋转表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！