sql-server-2005 - 翻转值并使其成为新表中的字段的 SQL 查询

Question

下面有这张表

如何让输出变成这样

我使用这个查询做了上面的输出:

SELECT DISTINCT 
    C.RefVal, 
    C.CalibrationEventID, 
    C1.Result As "Trial One", 
    C2.Result AS "Trial Two", 
    C3.Result AS "Trial Three", 
    C4.Result AS "Trial Four", 
    C5.Result AS "Trial Five" 
FROM CalibrationTrials AS C 
INNER JOIN 
    (SELECT DISTINCT RefVal, Result FROM CalibrationTrials WHERE CalibrationEventID = 'CAL000001' AND RefVal = '1.0010'AND TrialNo = 1) As C1 
ON C1.RefVal = C.RefVal
INNER JOIN 
    (SELECT DISTINCT RefVal, Result FROM CalibrationTrials WHERE CalibrationEventID = 'CAL000001' AND RefVal = '1.0010'AND TrialNo = 2) As C2 
ON C2.RefVal = C.RefVal
INNER JOIN 
    (SELECT DISTINCT RefVal, Result FROM CalibrationTrials WHERE CalibrationEventID = 'CAL000001' AND RefVal = '1.0010'AND TrialNo = 3) As C3 
ON C3.RefVal = C.RefVal
INNER JOIN 
    (SELECT DISTINCT RefVal, Result FROM CalibrationTrials WHERE CalibrationEventID = 'CAL000001' AND RefVal = '1.0010'AND TrialNo = 4) As C4 
ON C4.RefVal = C.RefVal
INNER JOIN 
    (SELECT DISTINCT RefVal, Result FROM CalibrationTrials WHERE CalibrationEventID = 'CAL000001' AND RefVal = '1.0010'AND TrialNo = 5) As C5 
ON C5.RefVal = C.RefVal

WHERE C.CalibrationEventID = 'CAL000001' AND C.RefVal = '1.0010'
ORDER BY C.RefVal, C.CalibrationEventID

显然对于此查询，输出仅固定为一个 RefVal，即“1.0010”

为 CalibrationEventID 的每个 RefVal 输出 image2 格式的正确查询是什么？

Answer 1

以下是使用 PIVOT 的方法您查询的子句:

SELECT
  RefVal,
  CalibrationEventID,
  [1] AS [Trial One],
  [2] AS [Trial Two],
  [3] AS [Trial Three],
  [4] AS [Trial Four],
  [5] AS [Trial Five],
  [6] AS [Trial Six]
FROM CalibrationTrials
PIVOT (
  MAX(Result) FOR TrialNo IN ([1], [2], [3], [4], [5], [6])
) AS p

以上假定您的表仅包含列 RefVal、CalibrationEventID、
试验编号，结果。如果表中的列较多，应先分别选择这四列，再应用PIVOT。这里:

SELECT
  RefVal,
  CalibrationEventID,
  [1] AS [Trial One],
  [2] AS [Trial Two],
  [3] AS [Trial Three],
  [4] AS [Trial Four],
  [5] AS [Trial Five],
  [6] AS [Trial Six]
FROM (
  SELECT
    RefVal,
    CalibrationEventID,
    TrialNo,
    Result
  FROM CalibrationTrials
) AS c
PIVOT (
  MAX(Result) FOR TrialNo IN ([1], [2], [3], [4], [5], [6])
) AS p

这是必需的，因为 PIVOT 查询本质上是一种特殊情况的 GROUP BY 查询。除Result 外的所有列都隐式 参与分组。这就是为什么您应该摆脱这种分组中不需要的那些。

还有另一种方法，没有 PIVOT:

SELECT
  RefVal,
  CalibrationEventID,
  MAX(CASE TrialNo WHEN 1 THEN Result END) AS [Trial One],
  MAX(CASE TrialNo WHEN 2 THEN Result END) AS [Trial Two],
  MAX(CASE TrialNo WHEN 3 THEN Result END) AS [Trial Three],
  MAX(CASE TrialNo WHEN 4 THEN Result END) AS [Trial Four],
  MAX(CASE TrialNo WHEN 5 THEN Result END) AS [Trial Five],
  MAX(CASE TrialNo WHEN 6 THEN Result END) AS [Trial Six]
FROM CalibrationTrials
GROUP BY
  RefVal,
  CalibrationEventID