Como posso simplesmente trocar de coluna com linhas em SQL? Existe algum comando simples para transpor?
ou seja, virar este resultado:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
nisto:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
O PIVOT
parece demasiado complexo para este cenário.
Há várias maneiras de transformar esses dados. Em seu post original, você afirmou que PIVOT
parece muito complexo para este cenário, mas pode ser aplicado muito facilmente utilizando as funções UNPIVOT
e PIVOT
no SQL Server.
Entretanto, se você não tiver acesso a essas funções, isso pode ser replicado utilizando UNION ALL
para UNPIVOT
e então uma função agregada com uma instrução CASE
para PIVOT
:
Criar tabela:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate e CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
A UNION ALL
realiza a UNPIVOT
dos dados transformando as colunas Paul, John, Tim, Eric
em filas separadas. Então você aplica a função agregada sum()
com a instrução case
para obter as novas colunas para cada color
.
Versão Estática do pivô e Pivô:
Tanto as funções UNPIVOT
e PIVOT
no SQL Server tornam esta transformação muito mais fácil. Se você conhece todos os valores que você quer transformar, você pode codificá-los em uma versão estática para obter o resultado:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
A consulta interna com o UNPIVOT
desempenha a mesma função que o UNION ALL
. Ela pega a lista de colunas e a transforma em filas, o PIVOT
então realiza a transformação final em colunas.
Versão Dinâmica Pivot:
Se você tem um número desconhecido de colunas (Paul, John, Tim, Eric
no seu exemplo) e então um número desconhecido de cores para transformar você pode utilizar sql dinâmico para gerar a lista para UNPIVOT
e então PIVOT
:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
A versão dinâmica consulta tanto a "sua mesa" quanto a tabela de "colunas" para gerar a lista de itens para "UNPIVOT" e "PIVOT". Isto é então adicionado a uma string de consulta a ser executada. A vantagem da versão dinâmica é que se você tiver uma lista de colors
e/ou names
, isto irá gerar a lista em tempo de execução.
Todas as três consultas produzirão o mesmo resultado:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Isto normalmente requer que você conheça TODOS os rótulos da coluna E da linha com antecedência. Como você pode ver na consulta abaixo, os rótulos estão todos listados em sua totalidade nas operações UNPIVOT e (re)PIVOT.
**MS SQL Server 2012 Schema Setup***:
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
**Query 1***:
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Baseado nesta solução de bluefeet aqui está um procedimento armazenado que usa sql dinâmico para gerar a tabela transposta. Ele requer que todos os campos sejam numéricos, exceto a coluna transposta (a coluna que será o cabeçalho na tabela resultante):
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
Você pode testá-lo com a tabela fornecida com este comando:
exec SQLTranspose 'yourTable', 'color'