Select multiple columns based on multiple conditions from single column-CodePudding

I have a table in a database, which stores amounts, their respective ‘invoice dates’, and what year of the contract those invoice dates fall in.

There are customers on annual, bi-annual, quarterly and monthly contracts. So there can be multiple invoices for one yearly period - hence the column defining which year the invoices fall in.

It looks similar to this:

ContractID	InvoiceNumber	InvoiceDate	Amount	YearIndex
1	1	01/01/2019	100	1
1	2	01/06/2019	100	1
1	3	01/01/2020	100	2
1	4	01/06/2020	100	2
2	1	01/03/2020	200	1
2	2	01/03/2020	200	2
2	3	01/03/2021	200	3
3	1	01/01/2020	300	1
3	2	01/04/2020	300	1
3	3	01/07/2020	300	1
3	4	01/10/2020	300	1

Ideally I want to run a query where I have something where I can see the sum of each contracts amounts, grouped by year index, e.g

ContractID	Year1Amount	Year2Amount	Year3Amount
1	200	200	NULL
2	200	200	200
3	1200	NULL	NULL

Is this possible from a single query? Or will this take joining multiple?

I’m working with Microsoft Access, in regards to available syntax.

Thank you in advance for any help.

CodePudding user response：

This is the general pattern. You can toggle the syntax for Access and Null vs. 0 based on what you need.

SELECT ContractID,
       SUM(CASE WHEN YearIndex = 1 THEN Amount ELSE 0 END) AS Year1Amount,
       SUM(CASE WHEN YearIndex = 2 THEN Amount ELSE 0 END) AS Year2Amount,
       ...
       SUM(CASE WHEN YearIndex = N THEN Amount ELSE 0 END) AS YearNAmount
  FROM SOME_TABLE
 GROUP
    BY ContactID