I have one table that contains data for several years, example is here:
| year | value | fruit |
|---|---|---|
| 2021 | 101 | apples |
| 2021 | 102 | bananas |
| 2021 | 103 | pears |
| 2021 | 104 | raspberries |
| 2021 | 105 | strawberries |
| 2022 | 201 | apples |
| 2022 | 202 | bananas |
| 2022 | 203 | pears |
| 2022 | 204 | raspberries |
| 2022 | 205 | strawberries |
| 2023 | 301 | apples |
| 2023 | 302 | bananas |
| 2023 | 303 | pears |
| 2023 | 304 | raspberries |
| 2023 | 305 | strawberries |
| 2024 | 401 | apples |
| 2024 | 402 | bananas |
| 2024 | 403 | pears |
| 2024 | 404 | raspberries |
| 2024 | 405 | strawberries |
I need a one-year-one-column table like the following:
| 2021 | 2022 | 2023 | 2024 | fruit |
|---|---|---|---|---|
| 101 | 201 | 301 | 401 | apples |
| 102 | 202 | 302 | 402 | bananas |
| 103 | 203 | 303 | 403 | pears |
| 104 | 204 | 304 | 404 | raspberries |
| 105 | 205 | 305 | 405 | strawberries |
I use as many JOINs and WHEREs as many years I want in the output table, like this:
SELECT fruits21.value as '2021'
, fruits22.value as '2022'
, fruits23.value as '2023'
, fruits24.value as '2024'
, fruits21.fruit
FROM fruits as fruits21
JOIN fruits as fruits22 ON fruits22.fruit = fruits21.fruit
JOIN fruits as fruits23 ON fruits23.fruit = fruits21.fruit
JOIN fruits as fruits24 ON fruits24.fruit = fruits21.fruit
WHERE fruits21.year = 2021
AND fruits22.year = 2022
AND fruits23.year = 2023
AND fruits24.year = 2024
Is there any other nice and short sql query to get the output table without using many JOINs and WHEREs (because there can be 100 years...)?
The answer 'No, there is no such a way' is sad but welcome anyway.
Thanks in advance!
