Select with PIVOT always returns null

Question

Select with PIVOT always returns null

Navigation

#1 by (2 votes)

1

I'm trying to make a query using the pivot based on this link insert the description of the link here , but without success, because it always returns 2 lines Null:

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 return like this:

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

However, it always returns Null.

sql sql-server pivot

asked by anonymous 18.10.2018 / 00:06

1 answer

Manage window switching Gtk + Doubt on a C language question

score 2 · Accepted Answer

You need to change the columns to be searched within 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 fiddle .