sql-server - 如何将相应的列添加到 PIVOT 数据集

Question

使用 Microsoft SQL Server 2012。

我有一张 table :

PartID |TypeSet | Setting | ObservID |TransDate
---------------------------------------------
 1         A    456       12    10/20/2015
 1         A    377       12    10/20/2015
 1         A    425       12    10/20/2015
 1         A    665       12    10/20/2015
 1         A    543       12    10/20/2015
 1         A    554       12    10/20/2015
 1         B    34        41    10/21/2015
 1         B    27        41    10/21/2015
 1         B    27        41    10/21/2015
 1         B    29        41    10/21/2015
 1         C    1299      12    10/20/2015
 1         C    1227      12    10/20/2015

我想将其显示如下。按 PartID 分组的单行。

PartID | A Avg | B Avg | C Avg |  A ObservID | B ObservID | C ObservID
 ---------------------------------------------------------------------
  1      503      29     1263        12             41         12

如果不创建多行返回数据集，我无法显示 ObservID。对于此查询，我对 A、B 和 C 平均值没有任何问题:

  SELECT    
      PartID
      ,[A] as 'A Average'
      ,[B] as 'B Average'   
      ,[C] as 'C Average'
     -- ,(ObservID)
     --,CASE WHEN [A] = 'A Average' THEN max([ObservID]) END as 'A Ob'
     --,CASE WHEN [B] = 'B Average' THEN max([ObservID]) END as 'B Ob'
     -- ,CASE WHEN [C] = 'C Average' THEN max([ObservID]) END as 'C Ob'
FROM
(SELECT
    PartID  
    ,TypeSet
    ,Setting
    --,ObservID
    FROM  #Temp1
    ) as MeasData
PIVOT
(
    AVG(Setting) 
    FOR [TypeSet] in 
        ( [A], [B], [C])    
) as PivotTable

您可以在上面的查询中看到我尝试在其中获取 ObservID，但是当我取消注释这些行时，会发生错误。一些注意事项:每个 TypeSet 的 ObservID 将保持一致。

这是数据:

create table #Temp1
(
  PartID INT
, TypeSet VARCHAR(10) 
, Setting INT
, ObservID INT
, TransDate Date
)

INSERT INTO #Temp1(PartID, TypeSet, Setting, ObservID, TransDate) VALUES
  (1, 'A', 456, 12, '10/20/2015'),
  (1, 'A', 377, 12, '10/20/2015'),
  (1, 'A', 425, 12, '10/20/2015'),
  (1, 'A', 665, 12, '10/20/2015'),
  (1, 'A', 543, 12, '10/20/2015'),
  (1, 'A', 554, 12, '10/20/2015'),
  (1, 'B', 34, 41, '10/21/2015'),
  (1, 'B', 27, 41, '10/21/2015'),
  (1, 'B', 27, 41, '10/21/2015'),
  (1, 'B', 29, 41, '10/21/2015'),
  (1, 'C', 1299, 12, '10/20/2015'),
  (1, 'C', 1227, 12, '10/20/2015')

通过 PIVOT 操作可以实现我想要的输出吗？或者我应该为每个排版走表上多个 JOIN 的路线？

Answer 1

您可以使用条件聚合来完成此操作:

SELECT PartID,
     AVG(CASE WHEN TypeSet = 'A' THEN Setting END) AS 'A Avg',
     AVG(CASE WHEN TypeSet = 'B' THEN Setting END) AS 'B Avg',
     AVG(CASE WHEN TypeSet = 'C' THEN Setting END) AS 'C Avg',
     MAX(CASE WHEN TypeSet = 'A' THEN ObservID END) AS 'A ObservID',
     MAX(CASE WHEN TypeSet = 'B' THEN ObservID END) AS 'B ObservID',
     MAX(CASE WHEN TypeSet = 'C' THEN ObservID END) AS 'C ObservID'
FROM #Temp1
GROUP BY PartID