How to reach the second table from the first using SQL Query?
1st Table:
| Date | CurrencyID | Rate |
|---|---|---|
| 2022/01/01 | 1 | 1000 |
| 2022/01/01 | 2 | 2000 |
| 2022/01/02 | 1 | 1000 |
| 2022/01/02 | 2 | 2000 |
| 2022/01/03 | 1 | 1000 |
| 2022/01/03 | 2 | 2000 |
2nd Table:
| Date | Currency1 | Currency2 |
|---|---|---|
| 2022/01/01 | 1000 | 2000 |
| 2022/01/02 | 1000 | 2000 |
| 2022/01/03 | 1000 | 2000 |
CodePudding user response：
select *
from
(
select Date, CurrencyID, Rate
) p
pivot (avg(p.Rate) for p.CurrencyID in ([1], [2])
) pvt
order by pvt.Date
Note that the avg(p.Rate) can be replaced by any other aggregate function - sum(), min(), max() etc. In this case the implication is that there is only one rate per currency per day, so there will be one row to aggregate (i.e. no actual aggregation happens).