在SQL Server中使用PIVOT [英] Using PIVOT in SQL Server
本文介绍了在SQL Server中使用PIVOT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何使用PIVOT在下面进行转换
How can i convert below using PIVOT
为
谢谢
推荐答案
有点混乱,但是就在这里.
It`s a bit messy, but here it is.
首先,我使用rank()函数来了解ErrorField的位置(1、2或3). 所有这些,我都使用该数字作为枢轴. 您需要两个支点以及它们之间的连接.
First of all I use the rank() function to know the position (1,2 or 3) of the ErrorField. With all that, I use that number for the Pivot. You need two pivots and a join between them.
WITH AuxTable (Data_Error_Key, ErrorField, ErrorValue, NumeroError)
AS
(
SELECT Data_Error_Key, ErrorField, ErrorValue, RANK() OVER (PARTITION BY Data_Error_Key ORDER BY Data_Error_Key, ErrorField)
FROM dbo.TempTable
)
SELECT TablaErrorField.Data_Error_Key, ErrorField1, ErrorValue1,ErrorField2,ErrorValue2, ErrorField3,
ErrorValue3
FROM
(
SELECT Data_Error_Key, [1] as ErrorField1, [2] as ErrorField2, [3] as ErrorField3
FROM (
SELECT Data_Error_Key,NumeroError, ErrorField
FROM AuxTable) P
PIVOT
(
MAX (ErrorField)
FOR NumeroError IN ([1], [2], [3])
) AS pvt) As TablaErrorField
INNER JOIN
(
SELECT Data_Error_Key, [1] as ErrorValue1, [2] as ErrorValue2, [3] as ErrorValue3
FROM (
SELECT Data_Error_Key,NumeroError, ErrorValue
FROM AuxTable) P
PIVOT
(
MAX (ErrorValue)
FOR NumeroError IN ([1], [2], [3])
) AS pvt) as TablaErrorValue
ON TablaErrorField.Data_Error_Key= TablaErrorValue.Data_Error_Key
所有这些假设您只需要3对ErrorField/ErrorValue.否则,您应该在此问题中查看我的答复.
All this assuming you need only 3 pairs of ErrorField/ErrorValue. Otherwise, you should take a look at my response in this question.
这篇关于在SQL Server中使用PIVOT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文