I have a table that has customer invoice header records, called Factura
, with information like this:
IdCliente | Fecha | NumeroFactura | y otros campos
-------------------------------------------------------
1 | 2021-01-01 | 201345 | ...
2 | 2021-01-01 | 201346 | ...
1 | 2021-01-17 | 203148 | ...
1 | 2021-01-31 | 207941 | ...
2 | 2021-02-11 | 209147 | ...
1 | 2021-02-28 | 211249 | ...
And I want to build a query that summarizes the number of invoices per customer for each month of the year, for 2021, with a structure like this:
IdCliente | Ene | Feb | Mar | Abr | May | Jun | Jul | Ago | Sep | Oct | Nov | Dic |
------------------------------------------------------------------------------------------------
1 | 3 | 1 | 7 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | 1 | 4 | 11 | 21 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
3 | 0 | 0 | 17 | 14 | 9 | 8 | 0 | 0 | 0 | 0 | 0 | 0 |
etc.
If the client's name could be included, even better.
How can I do that query?
You can use the clause
pivot
to summarize the data and at the same time rotate the information to display it in columns, the way you want.In cases like this, the code is worth a thousand words, the query is:
The parts that require explanation, in my opinion, are:
When
pivot
a query is given as a data source, in this query I take the opportunity to extract the month of each invoice numerically and filter the records of the year that interest us:In it
pivot
I use the functioncount()
to summarize the information in the way that interests us. Here we could also use other functions that add information, such assum()
oravg()
to produce reports with sales totals or average sales, to name a couple of examples.In the main select I am assigning aliases to the columns that, by then, already appear with the month number as title, like this,
1
esEne
,2
esFeb
, etc.For the month columns, I am forced to enclose their name in
[]
, since they are not valid identifiers, so the column1
can be referenced as[1]
without error.If you want to include the name of the client, my suggestion is to do a
join
after doing thepivot
, for this you can use a CTE, for example:You can achieve the goal with a more standard SQL statement, without using the clause
pivot
as I suggest in this other answerTo achieve this, we will apply two tricks :
case
that returns 1 for the records that are from the month of the column and 0 in any other case.In this way, we simulate the counter, and we get exactly the result we are looking for.
Finally we group by idCliente:
If we want to obtain the customer's name, we simply do the join to the Customer table and adjust the query to fetch the customer's name and group by said field, for example: