Error in SQL Pivot function

Asked

Viewed 107 times

1

First question: The Column that will become Row (in this case I specify the header) can be repeated ? I have several records that repeat, Example to Column Description:

inserir a descrição da imagem aqui

I’m trying to do it like this:

IF object_id('tempdb..#tab1') IS NOT NULL 
begin
    drop table #tab1
end

go

create table #tab1(
Id int null,
Nome varchar(50) null,
CNPJ varchar(50) null,
Descricao varchar(50) null,
Situacao varchar(50) null
)

insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (1,'Empresa1','8439659000150','Descricao1','Positivo')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (1,'Empresa1','8439659000150','Descricao2','Regular')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (1,'Empresa1','8439659000150','Descricao3','Irregular')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (2,'Empresa2','8439659000231','Descricao1','Positivo')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (2,'Empresa2','8439659000231','Descricao2','Regular')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (2,'Empresa2','8439659000231','Descricao3','Irregular')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (3,'Empresa3','11594952000105','Descricao1','Positivo')
insert into #tab1 (Id, Nome, CNPJ, Descricao, Situacao) values (3,'Empresa3','11594952000105','Descricao2','Regular')

SET NOCOUNT ON

DECLARE @COLUNAS VARCHAR(MAX)
SET @COLUNAS = ''

SELECT @COLUNAS = COALESCE(@COLUNAS + '[' + (CAST(Descricao AS NVARCHAR(255))) + '],','')
FROM (SELECT DISTINCT Descricao FROM #tab1) AS DADOS_HORIZONTAIS
SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1)

DECLARE @SQLSTRING NVARCHAR(500);

SET @SQLSTRING = N'
SELECT * FROM(SELECT Id, Nome, CNPJ, Descricao, Situacao FROM #tab1) AS DADOS_HORIZONTAIS
PIVOT(Situacao FOR Descricao IN('+@COLUNAS+')) AS PivotTable;'

 EXECUTE SP_EXECUTESQL @SQLSTRING

Another problem that occurs error:

Mensagem 156, Nível 15, Estado 1, Linha 13
Sintaxe incorreta próxima à palavra-chave 'FOR'.
sql-server

1 answer

1


Lack of aggregation function

You need to define at least one aggregation function for the PIVOT.

Example:

SET @SQLSTRING = N'
SELECT * FROM(SELECT Id, Nome, CNPJ, Descricao, Situacao FROM #tab1) AS DADOS_HORIZONTAIS
PIVOT(MIN(Situacao) FOR Descricao IN('+@COLUNAS+')) AS PivotTable;'

Browser other questions tagged sql-server

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