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:

Fig.1 — Expenses table — Initial Data

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:

Fig.2 — Pivot table — Final Data

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.