Pivot dynamic columns

Question

Pivot dynamic columns

Asked 10 years ago

Viewed 9,964 times

5

I need to display this:

inserir a descrição da imagem aqui

Thus:

inserir a descrição da imagem aqui

Following the instructions of the links s:

Use PIVOT operator without aggregation

I cannot place a subquery inside the IN in the PIVOT

Dynamic pivot with SQL Server

I came to this script:

create table #temp  (
ano int not null,
item int not null,
valor int
)

insert into #temp (ano,item, valor) values (2014,1,4400)
insert into #temp (ano,item, valor) values (2015,1,4500)
insert into #temp (ano,item, valor) values (2016,1,4600)
insert into #temp (ano,item, valor) values (2017,1,4700)

insert into #temp (ano,item, valor) values (2014,2,10600)
insert into #temp (ano,item, valor) values (2015,2,10900)
insert into #temp (ano,item, valor) values (2016,2,11000)
insert into #temp (ano,item, valor) values (2017,2,11300)

insert into #temp (ano,item, valor) values (2014,5,12000)
insert into #temp (ano,item, valor) values (2015,5,13000)
insert into #temp (ano,item, valor) values (2016,5,14000)
insert into #temp (ano,item, valor) values (2017,5,15000)

declare @cols nvarchar(max)
set @cols = stuff((
  select distinct ',' 
    + quotename(ano) 
  from #temp
  for xml path('')
), 1,1, '');

print @cols

declare @query as nvarchar(max)

set @query='SELECT * FROM (
        SELECT item
            , ano
            , valor
        FROM #temp
    ) AS t
    PIVOT (
        MAX(valor)
        FOR ano IN ( ' + @cols + ' ) 
    ) as P;';

print @query

execute(@query)

drop table #temp

Is this the way or would have a better way to address this issue?

Do you need to create the even temporary tables or already have them in the database? Also, there are some specific reasons why you are using the execute instead of mounting a query directly into sql server?

– rodrigogq

2014/12/17 at 02:25
@rodrigogq I only posted the example with temporary tables to facilitate execution and understanding. The tables will be in the bd. It would be several tables and all with this characteristic, data per year that should be displayed in spreadsheet form. By the amount of data and precise combinations of a solution where I get columns dynamically. I use the execute just to concatenate the columns dynamically. I researched a lot and found no other solution. I just wanted to make sure that I am on the right track, because as I said this solution will be applied on several occasions in the application.

– Jothaz

2014/12/17 at 11:24
got it. In this case I see no problem in its construction. Remember that if you wish the reverse operation can also use the UNPIVOT.. This is also a very common operation at the request of customers.

– rodrigogq

2014/12/17 at 12:56
@rodrigogq They are several historical with several indicators per year. I need to display it that way that can be analyzed and the best form and spreadsheet. It is essential to use a dynamic approach. I found the use of execute half "gambiarra", or a solution the way of Thor, in hammering. kkk However I saw no other approach in the researches I did. The post is to ensure that I am not complicating and could have a simpler and more elegant solution.

– Jothaz

2014/12/17 at 13:39
1

This torture only exists if you don’t know the years. If you know the code it’s super simple. The answer to your question is no, you cannot define the columns of the pivoting table dynamically.

– Nizam

2015/01/06 at 20:19
When the values that will be used as the header after the pivot are not known beforehand, then the "dynamic pivot" approach is used. Details in the article Alas & Pivots - turning rows into columns -> https://portosql.wordpress.com/2019/05/04/pivot/

– José Diz

2020/12/02 at 12:33

Show 1 more comment

2 answers

Browser other questions tagged sql database sql-server

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

by Diego Marques • **3,701** points · Answer 1 · 2020-03-11T20:06:03+00:00

It is really not possible in SQL Server to create a PIVOT with dynamic columns. DBMS needs columns to be set so you can transport rows to columns.

The only solution would be to actually generate this PIVOT query in string, either through a programming that already sends the ready query to SQL Server (C#, Python, PHP etc.) or within SQL Server itself by creating a Procedure or Script.

Official documentation: https://docs.microsoft.com/pt-br/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

by David • **4,330** points · Answer 2 · 2015-12-07T18:28:17+00:00

A possible solution would be, instead of turning into columns, the result of the columns that contain the XML year, and then working within your application:

SELECT * FROM (
    SELECT item
        , ano
        , valor
    FROM #temp
) AS t
PIVOT XML(
    MAX(valor)
    FOR ano IN (any) 
) as P;

Reference: http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html