UNPIVOT¶

Sintaxe¶

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

[ ... ]

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.

[ AS ] col_alias

Especifica o alias da coluna a ser usado no resultado da UNPIVOT Operação em vez dos nomes originais das colunas. Você não pode usar aliases diferentes para o mesmo nome de coluna. No entanto, você não pode usar o mesmo alias para vários nomes de colunas. A palavra-chave AS é opcional.

Notas de uso¶

• Não é possível usar uma junção LATERAL para fazer referência direta ao conjunto de resultados de uma operação UNPIVOT. A tentativa de fazer isso retorna um erro. Como alternativa, materialize o resultado de UNPIVOT primeiro em uma tabela temporária e depois faça referência a essa tabela na junção LATERAL. Para criar e carregar a tabela monthly_sales selecionada neste exemplo, consulte a seção de exemplos.

  A consulta a seguir não funciona porque LATERAL não pode fazer referência a um resultado de UNPIVOT definido diretamente:

  CopyExpand

  SELECT *
    FROM monthly_sales
      UNPIVOT (sales FOR month IN (jan, feb, mar, apr)) unpvt
      JOIN LATERAL (SELECT unpvt.sales AS sales_value) jl;
  

  Show lessSee more

  Scroll to top

  A seguinte instrução CREATE TEMPORARY TABLE cria uma tabela temporária para materializar o resultado de UNPIVOT. A consulta que segue essa instrução faz referência à tabela temporária na junção LATERAL:

  CopyExpand

  CREATE OR REPLACE TEMPORARY TABLE unpivot_result AS
    SELECT *
      FROM monthly_sales
        UNPIVOT (sales FOR month IN (jan, feb, mar, apr));
  
  SELECT *
    FROM unpivot_result
      JOIN LATERAL (SELECT unpivot_result.sales AS sales_value) jl;
  

  Show lessSee more

  Scroll to top

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;

+-------+-------------+-----+------+------+-----+
| 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;

+-------+-------------+-------+-------+
| 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 |
+-------+-------------+-------+-------+

O exemplo a seguir é o mesmo que o exemplo anterior, mas usa aliases para os nomes das colunas:

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

+-------+-------------+----------+-------+
| EMPID | DEPT        | MONTH    | SALES |
|-------+-------------+----------+-------|
|     1 | electronics | JANUARY  |   100 |
|     1 | electronics | FEBRUARY |   200 |
|     1 | electronics | MARCH    |   300 |
|     1 | electronics | APRIL    |   100 |
|     2 | clothes     | JANUARY  |   100 |
|     2 | clothes     | FEBRUARY |   300 |
|     2 | clothes     | MARCH    |   150 |
|     2 | clothes     | APRIL    |   200 |
|     3 | cars        | JANUARY  |   200 |
|     3 | cars        | FEBRUARY |   400 |
|     3 | cars        | MARCH    |   100 |
|     3 | cars        | APRIL    |    50 |
|     4 | appliances  | JANUARY  |   100 |
|     4 | appliances  | MARCH    |   100 |
|     4 | appliances  | APRIL    |    50 |
+-------+-------------+----------+-------+

A anterior SELECT As instruções excluem NULLs por padrão. Portanto, não incluem 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;

+-------+-------------+-------+-------+
| 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;

+-------------+-------+-------+
| 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 |
+-------------+-------+-------+