How to create a sample Pivot Table for your personal data?
Last week, I tried to organize my expenses in an excel. But after that, I remembered that I’m a software developer, so why not to use technology for it instead of an excel :P So, I decided to create a pivot table. How can you create a Pivot Table in T-SQL and what is it? Let’s see.
Concept
We use pivot tables when you need to convert your data from row-level to column-level. The Pivot Tables help us to generate an interactive table that combines and compares a large amount of data.
Let’s see an example
Imagine that you need to manage your personal expenses such as:
- House: 300€
- Car: 175€
- Phone: 25€
- Food: 150€
These expenses repeat every month .You can see an example below:

Note:
- In January they were made two phone payments (25€ and 10€).
- The Food expense is different in the two months.
Imagine you want to see the expenses per Category of all months like that:

How can we do it? Using a pivot table.
SELECT * FROM (
SELECT Category,
[Month],
Price
FROM Expenses
) AS ExpensesTable
PIVOT
(
SUM(Price)
FOR [Month] IN (Jan, Fev)
)AS pvt
I hope this helps you to create your first pivot table in T-SQL.
Thank you and good study.