时间戳

首页 > 解决方案 > 透视连接表

sql - 透视连接表

问题描述

我设置了三个表，如下所示

PETS TABLE
-------------------------
| PET_ID | NAME | COLOR |
| 1      | Jay  | Brown | 
| 2      | Bo   | Gray  |   

PETS_SHOTS TABLE
---------------------------------------------------------------
| ID | PET_ID | SHOT_ID | VACCINATED_ON | VACCINATION_EXPIRES |
| 1  | 1      | 5       | 2021-08-09    | 2024-08-09          | 
| 2  | 2      | 5       | 2021-02-13    | 2021-02-13          |
| 3  | 2      | 6       | 2019-04-23    | 2020-05-03          |

SHOTS TABLE
----------------------------
| ID | NAME       | YEARS | 
| 5  | Rabies     | 1     | 
| 6  | Bordetella | 2     | 
| 7  | Distemper  | 2     |

我正在寻找一个可以给我以下结果的查询。甚至可以为表中的每一行自动生成列SHOTS吗？

-----------------------------------------------------------------------------------
| PET_ID | NAME | COLOR | RABIES_EXPIRES | BORDETELLA_EXPIRES | DISTEMPER_EXPIRES |
| 1      | Jay  | Brown | 2024-08-09     | 2021-02-13         |                   |
| 2      | Bo   | Gray  | 2021-02-13     | 2020-05-03         |                   |

我最初的想法是像这样对子句中SHOT的每种类型使用子查询SELECT

SELECT
    P.PtOwnerCode AS "owner_id",
    P.PtPetName AS "name",
    P.PtLast AS "last_visit",
    P.PtNotes AS "notes",
    P.PtWarning AS "warning",
    (
        SELECT
            PS.PtShVacDate
        FROM
            PetShots AS PS
            INNER JOIN Shots S ON PS.PtShShot = S.ShSeq
                AND PS.PtShPet = P.PtSeq
        WHERE
            S.ShName = 'Bordetella') AS "bordetella"
    FROM
        Pets AS P

当我意识到该表中可能有 30 多个条目时，这很快就失控了。

标签： sqlsql-servertsqljoinpivot

解决方案

您可以使用条件聚合：

SELECT p.*,
       ps.Bordetella, ps.Rabies, . . .
FROM Pets P LEFT JOIN
     (SELECT PS.PtShPet,
             MAX(CASE WHEN S.ShName = 'Bordetella' THEN PS.PtShVacDate END) as Bordetella,
             MAX(CASE WHEN S.ShName = 'Rabies' THEN PS.PtShVacDate END) as Rabies,
             . . . 
      FROM PetShots PS JOIN
           Shots S
           ON PS.PtShShot = S.ShSeq
      GROUP BY PS.PtShPet
     ) PS
     ON PS.PtShPet = P.PtSeq;

推荐阅读