- Categorias:
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:
Sintaxe¶
SELECT ...
FROM ...
UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]
( <value_column>
FOR <name_column> IN ( <column_list> ) )
[ ... ]
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 preenchervalue_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;
+-------+-------------+-----+------+------+-----+
| 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 |
+-------+-------------+-------+-------+
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;
+-------+-------------+-------+-------+
| 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 |
+-------------+-------+-------+