Categorias:: Sintaxe de consulta

UNPIVOT¶

Gira uma tabela transformando colunas em linhas. UNPIVOT é um operador relacional que aceita duas colunas (de uma tabela ou subconsulta), juntamente com uma lista de colunas, e gera uma linha para cada coluna especificada na lista. Em uma consulta, ela é especificada na cláusula FROM após o nome da tabela ou subconsulta.

UNPIVOT não é exatamente o contrário de PIVOT, pois não pode desfazer as agregações feitas por PIVOT.

Este operador pode ser usado para transformar uma tabela larga (por exemplo, empid, jan_sales, feb_sales, mar_sales) em uma tabela mais estreita (por exemplo, empid, month, sales).

Consulte também:: PIVOT

Sintaxe¶

SELECT ...
FROM ...
    UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]
      ( <value_column>
        FOR <name_column> IN ( <column_list> ) )

[ ... ]

Copy

Parâmetros¶

{ INCLUDE | EXCLUDE } NULLS

Especifica se deve incluir ou excluir linhas com NULLs no name_column:

INCLUDE NULLS inclui linhas com NULLs.
EXCLUDE NULLS exclui linhas com NULLs.

Padrão: EXCLUDE NULLS

value_column

O nome a ser atribuído à coluna gerada que será preenchida com os valores das colunas da lista de colunas.

name_column

O nome a ser atribuído à coluna gerada que será preenchida com os nomes das colunas na lista de colunas.

column_list

Os nomes das colunas na tabela ou subconsulta de origem que serão giradas em uma única coluna pivô. Os nomes das colunas irão preencher name_column, e os valores das colunas irão preencher value_column.

O column_list pode conter apenas nomes de colunas literais, não uma subconsulta.

As colunas em column_list devem ter exatamente o mesmo tipo de dados, com as seguintes exceções:

Os tipos de dados para cadeias de caracteres de texto podem ter comprimentos diferentes.
Se as colunas contiverem cadeias de caracteres de texto, colunas diferentes poderão usar tipos de dados diferentes para o texto. Por exemplo, a lista pode incluir uma coluna VARCHAR e uma coluna CHAR.

Exemplos¶

Crie uma tabela, monthly_sales, com a seguinte estrutura e dados:

CREATE OR REPLACE TABLE monthly_sales(
  empid INT,
  dept TEXT,
  jan INT,
  feb INT,
  mar INT,
  apr INT);

INSERT INTO monthly_sales VALUES
  (1, 'electronics', 100, 200, 300, 100),
  (2, 'clothes', 100, 300, 150, 200),
  (3, 'cars', 200, 400, 100, 50),
  (4, 'appliances', 100, NULL, 100, 50);

SELECT * FROM monthly_sales;

Copy

+-------+-------------+-----+------+------+-----+
| EMPID | DEPT        | JAN | FEB  | MAR  | APR |
|-------+-------------+-----+------+------+-----|
|     1 | electronics | 100 | 200  | 300  | 100 |
|     2 | clothes     | 100 | 300  | 150  | 200 |
|     3 | cars        | 200 | 400  | 100  |  50 |
|     4 | appliances  | 100 | NULL | 100  |  50 |
+-------+-------------+-----+------+------+-----+

Desarticule as colunas individuais do mês para retornar um único valor sales por month para cada funcionário.

SELECT *
  FROM monthly_sales
    UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

A instrução SELECT anterior exclui NULLs por padrão. Portanto, não inclui nos resultados uma linha de eletrodomésticos em fevereiro. Para incluir NULLs nos resultados, execute a seguinte instrução SQL:

SELECT *
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | FEB   |  NULL |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

Esta saída inclui uma linha para eletrodomésticos em fevereiro.

Em vez de selecionar todas as colunas com *, é possível incluir colunas específicas na lista SELECT e referenciar UNPIVOT value_column e name_column. O exemplo a seguir é semelhante ao exemplo anterior, mas especifica value_column sales e name_column month na lista SELECT. A consulta exclui a coluna empid:

SELECT dept, month, sales
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY dept;

Copy

+-------------+-------+-------+
| DEPT        | MONTH | SALES |
|-------------+-------+-------|
| appliances  | JAN   |   100 |
| appliances  | FEB   |  NULL |
| appliances  | MAR   |   100 |
| appliances  | APR   |    50 |
| cars        | JAN   |   200 |
| cars        | FEB   |   400 |
| cars        | MAR   |   100 |
| cars        | APR   |    50 |
| clothes     | JAN   |   100 |
| clothes     | FEB   |   300 |
| clothes     | MAR   |   150 |
| clothes     | APR   |   200 |
| electronics | JAN   |   100 |
| electronics | FEB   |   200 |
| electronics | MAR   |   300 |
| electronics | APR   |   100 |
+-------------+-------+-------+