Pivot dynamic columns

Question

Pivot dynamic columns

Navigation

#1 by (-1 votes)

3

I need to display this:

So:

Following the link's instructions:

Using the PIVOT operator without aggregation

I can not place a subquery inside the IN in 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 the path the same or would it be a better way to address this issue?

sql sql-server

asked by anonymous 16.12.2014 / 20:00

1 answer

Two parameters with String.Format in an asp: HyperLink Error in "utf-8" in python 3

score -1 · Answer 1

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

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

Reference: link