How to transpose the result of a search using SQL Server?

Asked

Viewed 380 times

5

Or better saying how to make the columns of a search result SQL Server, become lines of the result, as follows in the images below:

A search result using the clause select whichever:

inserir a descrição da imagem aqui

Make the result look like this:

inserir a descrição da imagem aqui

Note: Remembering that I have knowledge that I can do this with UNPIVOT, I’m looking for more solutions that I can solve this.

Thanks in advance.

sql database sql-server
  • 2

    Using pivot. Look at this link of OS.

  • 1

    In my case I have to use UNPIVOT, because I want to turn columns into rows, and in the case of PIVOT it turns rows into columns.

2 answers

4


There are 3 ways to do:

  • Union All, Aggregate and Case.
  • Unpivot and Pivot Static.
  • Dynamic Pivot.

But in matters of performance, Pivot and Unpivot are the recomentados, because they are proper functions for this.

I could give examples, however it is recommended to use the pivot, so I will leave that link just out of curiosity.

Finally, there is no simple solution to do this in SQL.

1

Taking as a basis, where we have a Box table and taking into account that we want to show the balance month by month of all years we would initially have a query that returns the value, the month and the 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 turn these results into columns, just use the pivot, then stay 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

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.