sql - 如何获取数据透视表每行的最大列数和最小列数？

Question

非常感谢@JohnCappelletti正如他展示了如何旋转表格一样。

这是一个示例数据:

DECLARE @OperatorPrice TABLE (ID int NOT NULL, OperatorId INT NULL, Price 
    NUMERIC(18,3) NULL, FName VARCHAR(50) NULL)

INSERT INTO @OperatorPrice (
    ID, OperatorId, Price, FName
)
VALUES
(226, 996, 22954,'Operator1')
, (266, 1016, 79011.2,   'Operator3')
, (112, 1029, 14869,     'Operator4')
, (112, 996, 22954,      'Operator1')
, (93,   1031, 10568.96, 'Operator5')


DECLARE @TR TABLE 
(
      ID varchar(25) NULL  
    , MinPrice DECIMAL(18,3) NULL, MaxPrice DECIMAL(18,3) NULL
    , SumCount DECIMAL(18,3) NULL 
    , Operator1  DECIMAL(18,3) NULL, OC1  DECIMAL(18,3) NULL, Operator2  
        DECIMAL(18,3) NULL, 
    OC2  DECIMAL(18,3) NULL, Operator3  DECIMAL(18,3) NULL, OC3  
    DECIMAL(18,3) NULL, 
    Operator4  DECIMAL(18,3) NULL, OC4  DECIMAL(18,3) NULL, Operator5  
    DECIMAL(18,3) NULL, 
    OC5  DECIMAL(18,3) NULL
)

枢轴代码:

INSERT @TR
SELECT *
FROM  (
    Select B.*
    From  @OperatorPrice A
    Cross Apply ( values  (0,FName,Price)
                     , (0,'OC'+replace(FName,'Operator',''),OperatorID)
                     , (A.ID,'MinPrice', A.Price)
                     , (A.ID,'MaxPrice', A.Price)
                     , (A.ID,'SumCount', A.OperatorId)
                     , (A.ID,FName,Price)
                     , (A.ID,'OC'+replace(FName,'Operator',''),OperatorID)
             ) B (ID,Item,Value)
     Union All
     Select
         ID=0
         , B.*
     From ( Select Top 50 N=Row_Number() Over (Order By (Select NULL)) 
         From master..spt_values n1 ) A
     Cross Apply ( values (concat('Operator',N),NULL)
                     ,(concat('OC',N),NULL)
             ) B (Item,Value)
  ) AS SourceTable        
  PIVOT  ( sum(Value) FOR Item IN (MinPrice, MaxPrice, SumCount,Operator1, 
      OC1, Operator2, OC2,  
      Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

  SELECT * FROM @TR

除了 MinPrice 和 MaxPrice 是错误的，上面的代码工作完美!目前它们是 Sum() 的 Price:

但我需要价格的 Min() 和 Price 列的 Max()。 因此，所需的输出应如下所示:

如何获取数据透视表行的Min()和Price列的Max()？

Answer 1

Gordon 是正确的，因为您正在混合聚合，所以条件聚合可能更高效。

但是，通过添加几个UNION ALLs，我们可以得到想要的结果

示例

DECLARE @OperatorPrice TABLE (ID int NOT NULL, OperatorId INT NULL, Price 
    NUMERIC(18,3) NULL, FName VARCHAR(50) NULL)

INSERT INTO @OperatorPrice (
    ID, OperatorId, Price, FName
)
VALUES
(226, 996, 22954,'Operator1')
, (266, 1016, 79011.2,   'Operator3')
, (112, 1029, 14869,     'Operator4')
, (112, 996, 22954,      'Operator1')
, (93,   1031, 10568.96, 'Operator5')


DECLARE @TR TABLE 
(
      ID varchar(25) NULL  
    , MinPrice DECIMAL(18,3) NULL, MaxPrice DECIMAL(18,3) NULL
    , SumCount DECIMAL(18,3) NULL 
    , Operator1  DECIMAL(18,3) NULL, OC1  DECIMAL(18,3) NULL, Operator2  
        DECIMAL(18,3) NULL, 
    OC2  DECIMAL(18,3) NULL, Operator3  DECIMAL(18,3) NULL, OC3  
    DECIMAL(18,3) NULL, 
    Operator4  DECIMAL(18,3) NULL, OC4  DECIMAL(18,3) NULL, Operator5  
    DECIMAL(18,3) NULL, 
    OC5  DECIMAL(18,3) NULL
)



INSERT @TR
SELECT *
FROM  (
    Select B.*
    From  @OperatorPrice A
    Cross Apply ( values  (0,FName,Price)
                        , (0,'OC'+replace(FName,'Operator',''),OperatorID)
                        , (A.ID,'SumCount', A.OperatorId)
                        , (A.ID,FName,Price)
                        , (A.ID,'OC'+replace(FName,'Operator',''),OperatorID)
             ) B (ID,Item,Value)
     Union All 
     Select ID,Item='MinPrice',Value=min(Price) From @OperatorPrice Group By ID
     Union All
     Select ID,Item='MaxPrice',Value=max(Price) From @OperatorPrice Group By ID
     Union All
     Select 0,Item='MinPrice',Value=min(Price) From @OperatorPrice 
     Union All
     Select 0,Item='MaxPrice',Value=max(Price) From @OperatorPrice
     Union All
     Select 0,Item='SumCount',Value=sum(OperatorId) From @OperatorPrice
     Union All
     Select
         ID=0
         , B.*
     From ( Select Top 50 N=Row_Number() Over (Order By (Select NULL)) 
         From master..spt_values n1 ) A
     Cross Apply ( values (concat('Operator',N),NULL)
                     ,(concat('OC',N),NULL)
             ) B (Item,Value)
  ) AS SourceTable        
  PIVOT  ( sum(Value) FOR Item IN (MinPrice, MaxPrice, SumCount,Operator1, 
      OC1, Operator2, OC2,  
      Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

  SELECT * FROM @TR

返回

注意:我为 ID 0 添加了 Total Min, Max, SumCount