I have a table and I need to insert the data from that table into another blank table in a certain way.
| Year | LoanType | ProcessDate | Month | Balance | RowNum | TypeRow | PercentChange | LastCol |
|---|---|---|---|---|---|---|---|---|
| 2022 | 0 | 20220430 | 04 | 500 | 1 | 1 | -1.22 | 450 |
| 2022 | 0 | 20220331 | 03 | 450 | 2 | 2 | 1.01 | 200 |
| 2022 | 0 | 20220228 | 02 | 200 | 3 | 3 | -.012 | 600 |
| 2022 | 0 | 20220128 | 01 | 200 | 4 | 4 | -.012 | 600 |
| 2022 | 0 | 20221228 | 12 | 200 | 5 | 5 | -.012 | 600 |
| 2022 | 0 | 20211128 | 11 | 200 | 6 | 6 | -.012 | 600 |
| 2022 | 0 | 20211028 | 10 | 200 | 7 | 7 | -.012 | 600 |
| 2022 | 0 | 20210928 | 09 | 200 | 8 | 8 | -.012 | 600 |
| 2022 | 0 | 20210828 | 08 | 200 | 9 | 9 | -.012 | 600 |
| 2022 | 0 | 20210728 | 07 | 200 | 10 | 10 | -.012 | 600 |
| 2022 | 0 | 20210628 | 06 | 200 | 11 | 11 | -.012 | 600 |
| 2021 | 0 | 20210528 | 05 | 200 | 12 | 12 | -.012 | 600 |
| 2021 | 0 | 20210428 | 04 | 200 | 13 | 13 | -.012 | 600 |
| 2022 | 1 | 20220430 | 04 | 500 | 1 | 1 | -1.22 | 450 |
| 2022 | 1 | 20220331 | 03 | 450 | 2 | 2 | 1.01 | 200 |
| 2022 | 1 | 20220228 | 02 | 200 | 3 | 3 | -.012 | 600 |
| 2022 | 1 | 20220128 | 01 | 200 | 4 | 4 | -.012 | 600 |
| 2022 | 1 | 20221228 | 12 | 200 | 5 | 5 | -.012 | 600 |
| 2022 | 1 | 20211128 | 11 | 200 | 6 | 6 | -.012 | 600 |
| 2022 | 1 | 20211028 | 10 | 200 | 7 | 7 | -.012 | 600 |
| 2022 | 1 | 20210928 | 09 | 200 | 8 | 8 | -.012 | 600 |
| 2022 | 1 | 20210828 | 08 | 200 | 9 | 9 | -.012 | 600 |
| 2022 | 1 | 20210728 | 07 | 200 | 10 | 10 | -.012 | 600 |
| 2022 | 1 | 20210628 | 06 | 200 | 11 | 11 | -.012 | 600 |
| 2021 | 1 | 20210528 | 05 | 200 | 12 | 12 | -.012 | 600 |
| 2021 | 1 | 20210428 | 04 | 200 | 13 | 13 | -.012 | 600 |
So what is happening here is that there are different "Loantypes"
The table I want to stick them in is like this:
| Year | LoanType | Jan | Feb | Mar | April | May | June | July | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2022 | 0 | 1.1 | -2.5 | 5.1 | .12 | ||||||||
| 2021 | 0 | 1.1 | -2.5 | 5.1 | .12 | .1 | -1.22 | 4.50 | -1.22 | -1.22 | -1.22 | -1.22 | -1.22 |
| 2022 | 1 | 1.1 | -2.5 | 5.1 | .12 | ||||||||
| 2021 | 1 | 1.1 | -2.5 | 5.1 | .12 | .1 | -1.22 | 4.50 | -1.22 | -1.22 | -1.22 | -1.22 | -1.22 |
So I need to insert each PercentChange into where it belongs based on the year and on the loanType as seen here above with just random numbers.
I tried using a case statement to insert values in but it gives each Row its own row with zeros for every other month.
select * from #LoanT
order by LoanType asc, RowNum asc
>Solution :
You can use conditional aggregation:
select year
, loantype
, jan = min(case when month = 1 then percentchange end)
, ...
, dec = min(case when month = 12 then percentchange end)
from loan
group by year, loantype