Or rather telling you how to make the columns of the result of a SQL Server search result become the result lines, as follows in the images below:
A search result using the select clause:
Maketheresultlooklikethis:
Note: Remembering that I have knowledge that I can do this with UNPIVOT, I am wanting more solutions that I can solve this.
Thank you in advance.
There are 3 ways to do it:
But in terms of performance issues, Pivot and Unpivot are the ones that are recommended, even because they are their own functions.
I could give examples, but it is recommended to use the pivot, so I'll let this link just for curiosity.
Finally, there is no simple solution to doing this in SQL.
Based on where we have a box table and taking into consideration that we want to show the month-to-month balance of all years we would initially have a query that returns the value, month and year
select a.Valor,
datepart(month, a.DataHora) as Mes,
datepart(year, a.DataHora) Ano
from CaixaCorrido a) as Caixa
but in this query the result would be in rows, to transform these results into columns, just use the pivot , then be as follows
select *
from (
select a.Valor,
datepart(month, a.DataHora) as Mes,
datepart(year, a.DataHora) Ano
from CaixaCorrido a) as Caixa
pivot
(
sum(Valor)
for Mes
in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) Caixa