如何在SQL Server中编写查询，为每个唯一ID的每个变量返回一列？以下是我要完成的工作的简化示例。您能为我指明正确的方向吗？
Table_Exists
ID | LOCATION | STATUS
__________________________________
001 | top | 3
001 | mid | 1
001 | bot | 4
002 | top | 2
002 | mid | 2
002 | bot | 1
003 | top | 1
003 | mid | 4
003 | bot | 2
这是我正在尝试的简化示例，显然没有成功。下面的代码返回错误消息，提示“ =”周围的语法不正确：
SELECT
ID,
LOCATION = top AS Top,
LOCATION = mid AS Middle,
LOCATION = bot AS Bottom
FROM
Table_Exists
我试图建立：
Table_Desired
ID | Top | Middle | Bottom
__________________________
001| 3 | 1 | 4
002| 4 | 2 | 2
003| 1 | 4 | 2
您可以使用自我联接（只要您LOCATION是自我联接NOT NULL并且它包含所有top/mid/bot值）：
SELECT t1.ID, t1.Status AS Top, t2.Status AS Middle, t3.Status AS Bottom
FROM Table_Exists t1
JOIN Table_Exists t2
ON t1.ID = t2.ID
AND t1.LOCATION = 'top'
AND t2.Location = 'mid'
JOIN Table_Exists t3
ON t1.ID = t3.ID
AND t1.LOCATION = 'top'
AND t3.Location = 'bot';
对于更通用的解决方案，请使用PIVOT：
SELECT
[ID]
,[Top] = [top]
,[Middle] = [mid]
,[Bottom] = [bot]
FROM #Table_Exists t
PIVOT (
MAX(STATUS) FOR LOCATION IN ([top],[mid],[bot])
) AS Piv
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句