Select with PIVOT always returns null

Asked

Viewed 182 times

1

I’m trying to do a query using the pivot based on this link insert link description here, but without success, as it always returns 2 Null lines:

Query: 

DECLARE @registros as table (
    ID  int,
    Campo varchar(250),
    Valor varchar(250)
)
INSERT INTO @registros VALUES
(1,'Pesquisar A','CRIAR'),
(1,'Pesquisar A','ATUALIZAR'),
(1,'Pesquisar A','DELETAR'),
(1,'Pesquisar A','PESQUISAR'),
(2,'Pesquisa B','CRIAR'),
(2,'Pesquisa B','ATUALIZAR')

SELECT * 
FROM @registros
PIVOT (
    MAX(Campo)
    FOR Valor IN
    ([NMFUNCIONALIDADE], [NMACOES])
) AS pvt
ORDER BY ID

I need you to come back like this: 

|ID|Campo        |Valor  |Valor      |Valor    |Valor      |
|1 |'Pesquisar A'|'CRIAR'|'ATUALIZAR'|'DELETAR'|'PESQUISAR'|
|2 |'Pesquisar B'|'CRIAR'|'ATUALIZAR'|NULL     |NULL       |

However always returns Null.

sql database sql-server pivot

1 answer

2


You need to change the columns to be searched within the pivot:

DECLARE @registros as table (
    ID  int,
    Campo varchar(250),
    Valor varchar(250)
)
INSERT INTO @registros VALUES
(1,'Pesquisar A','CRIAR'),
(1,'Pesquisar A','ATUALIZAR'),
(1,'Pesquisar A','DELETAR'),
(1,'Pesquisar A','PESQUISAR'),
(2,'Pesquisa B','CRIAR'),
(2,'Pesquisa B','ATUALIZAR')

SELECT * 
FROM @registros
PIVOT (
    MAX(Campo)
    FOR Valor IN
    ([CRIAR], [ATUALIZAR], [DELETAR], [PESQUISAR])
) AS pvt
ORDER BY ID

Take a look at the fiddle.

Browser other questions tagged sql database sql-server pivot

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