Home > Blockchain >  Insert a column from one table another as a row based on months and years defined in each column
Insert a column from one table another as a row based on months and years defined in each column

Time:05-13

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

CodePudding user response:

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