代码之家 › 专栏 › 技术社区 › StepUp

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

pivot tsql sql-server sql

StepUp · 技术社区 · 7 年前

非常感谢 @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() 价格和 Max() 列。 因此,所需的输出应如下所示:

最小() 价格和 最大值() 属于 价格 数据透视表行的列?

2 回复 | 直到 7 年前

John Cappelletti 7 年前

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

退换商品

hkravitz 7 年前

您可以用以下内容包装整个代码:

SELECT CASE WHEN MinPrice IS NOT NULL THEN Mn END MinPrice,
       CASE WHEN MaxPrice IS NOT NULL THEN Mx END MaxPrice,
SumCount , Operator1 , OC1 , Operator2 , OC2 , Operator3 , OC3 ,Operator4 , OC4 ,Operator5 , OC5
 FROM 
 (--This is where your part starts
    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 --This is where your part ends
    ) main
CROSS APPLY 
(
SELECT  MIN(X.t) Mn , MAX(X.t) Mx 
FROM 
    (
    VALUES (Operator1) , (Operator2) , (Operator3) , (Operator4) , (Operator5)
    ) x (t)
) Q