SQL - spread previous values from one column into multiple new columns

SQL - spread previous values from one column into multiple new columns

I have a SQL table of Customer_ID, showing Payments by Year. The first (of many) customer appears like this:

I have a SQL table of Customer_ID, showing Payments by Year. The first (of many) customer appears like this:

 ID    Payment    Year
112          0    2004
112          0    2005
112          0    2006
112       9592    2007
112      12332    2008
112       9234    2011
112       5400    2012
112       7392    2014
112       8321    2015

Note that some years are missing. I need to create 10 new columns, showing the Payments in the previous 10 years, for each row. The resulting table should look like this:

 ID    Payment    Year   T-1  T-2  T-3  T-4  T-5  T-6  T-7  T-8  T-9 T-10   
112          0    2004  NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
112          0    2005     0 NULL NULL NULL NULL NULL NULL NULL NULL NULL
112          0    2006     0    0 NULL NULL NULL NULL NULL NULL NULL NULL
112        952    2007     0    0    0 NULL NULL NULL NULL NULL NULL NULL
112       1232    2008   952    0    0    0 NULL NULL NULL NULL NULL NULL
112        924    2011  NULL NULL 1232  952    0    0    0 NULL NULL NULL 
112        500    2012   924 NULL NULL 1232  952    0    0    0 NULL NULL 
112        392    2014  NULL  500  924 NULL NULL 1232  952    0    0    0
112        821    2015   392 NULL  500  924 NULL NULL 1232  952    0    0

I am well aware that this is a large duplication of data, and so seems like a strange thing to do. However, I would still like to do it! (the data is being prepared for a predictive model, in which previous payments (and other info) will be used to predict the current year's payment)

I'm not really sure where to start with this. I have been looking at using pivot, but can't figure out how to get it to select values from a customer's previous year.

I would very much like to do this in SQL. If that is not possible I may be able to copy the table into R - but SQL is my preference.

Any help much appreciated.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

​ I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created *JudgeMyTaste*, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly. ​ The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t *have* to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using! ​ The tools I’ll be using that are available for all platforms are: * SQL Server 2017 (I’ll be running on Ubuntu 18.04) * Visual Studio Code * Azure Data Studio * ASP.NET Core 2.2 ​ Once you’ve got all the tools installed for your platform, let’s rock and roll! ​ ​ ​ ## Scaffold Your ASP.NET Core 2.2 Application ​ No matter the platform you’re on, the ```dotnet``` CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it: I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.