非常感谢@约翰卡佩莱蒂 https://stackoverflow.com/users/1570000/john-cappelletti他展示了如何旋转桌子。
这是一个示例数据:
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
and MaxPrice
错了!目前他们是Sum()
of Price
:
但是我需要Min()
的价格和Max()
of Price
柱子。所以期望的输出应该是这样的:
如何获得Min()
的价格和Max()
of Price
数据透视表的行的列?