CODEWITHSUNDEEP
sqlsql-servert-sqljoinpivot
Youssef Elkhayami
Youssef Elkhayami

Reputation: 13

Pivoting a join table

I have three tables set up like below

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     |

I'm looking for a query that will give me the following result. Is it even possible to automatically generate columns for each row in the SHOTS table?

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

My initial thought was using subqueries for each type of SHOT in the SELECT clause like so

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

This got out of hand pretty quickly when I realised there could be 30+ entries in that table.

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation:

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;

Upvotes: 1

Related Questions