Compreender a função PIVOT em T-SQL
tenho uma mesa como esta:
ID | TeamID | UserID | ElementID | PhaseID | Effort
-----------------------------------------------------
1 | 1 | 1 | 3 | 5 | 6.74
2 | 1 | 1 | 3 | 6 | 8.25
3 | 1 | 1 | 4 | 1 | 2.23
4 | 1 | 1 | 4 | 5 | 6.8
5 | 1 | 1 | 4 | 6 | 1.5
ElementID | PhaseID1 | PhaseID5 | PhaseID6
--------------------------------------------
3 | NULL | 6.74 | 8.25
4 | 2.23 | 6.8 | 1.5
5 answers
A PIVOT usado para rodar os dados de uma coluna para várias colunas.
Para o seu exemplo, aqui está um pivô estático, o que significa que deseja codificar as colunas que deseja rodar:
create table temp
(
id int,
teamid int,
userid int,
elementid int,
phaseid int,
effort decimal(10, 5)
)
insert into temp values (1,1,1,3,5,6.74)
insert into temp values (2,1,1,3,6,8.25)
insert into temp values (3,1,1,4,1,2.23)
insert into temp values (4,1,1,4,5,6.8)
insert into temp values (5,1,1,4,6,1.5)
select elementid
, [1] as phaseid1
, [5] as phaseid5
, [6] as phaseid6
from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in([1], [5], [6])
)p
Aqui está uma DemoSQL com uma versão de trabalho.
Isto também pode ser feito através de um pivô dinâmico onde você cria a lista de colunas dinamicamente e executa o pivô.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT elementid, ' + @cols + ' from
(
select elementid, phaseid, effort
from temp
) x
pivot
(
max(effort)
for phaseid in (' + @cols + ')
) p '
execute(@query)
Os resultados para ambos:
ELEMENTID PHASEID1 PHASEID5 PHASEID6
3 Null 6.74 8.25
4 2.23 6.8 1.5
Estes são os exemplos básicos do pivô, por favor, passem por isso.
Exemplos de tabelas SQL SERVER – PIVOT e UNPIVOT
Exemplo da relação acima para a tabela de produtos:
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
Renders:
PRODUCT FRED KATE
--------------------
BEER 24 12
MILK 3 1
SODA NULL 6
VEG NULL 5
Exemplos semelhantes podem ser encontrados na postagem do blog tabelas Pivot no servidor SQL. Uma amostra simples
Para definir o erro de compatibilidade
Use isto antes de usar a função pivot
ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100
Na solução de @ bluefeet, é importante mencionar que {[[0]} é a coluna chave do seu "invisível" Group By.
Além disso, você pode substituir elementid ou adicionar mais colunas como userid.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Here is the result set.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105