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

如何将两列数据透视到预定义的表中?

pivot tsql sql-server

StepUp · 技术社区 · 6 年前

我见过 this question , this 和 this 但是这些不是我想要的。

请不要结束我的问题,因为它不是重复的。这对我来说真的很重要。

我已成功透视表,但这不是预期结果:

预期结果如下所示:

OperatorX OC

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')
, (93,   1031, 10568.96, 'Operator5')


DECLARE @TR TABLE 
(
 ID INT 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
(ID , 
Operator1, OC1, Operator2, OC2,  Operator3, OC3,  Operator4, OC4,  
Operator5, OC5)
SELECT ID , 
    Operator1, OC1, Operator2, OC2,  Operator3, OC3,  Operator4, OC4,  
Operator5, OC5
FROM 
(SELECT Price, id, FName 
FROM @OperatorPrice) AS SourceTable 
PIVOT 
( 
   sum(Price)
   FOR FName IN (Operator1, OC1, Operator2, OC2,  Operator3, OC3,  
   Operator4, OC4,  Operator5, OC5) 
) AS PivotTable

SELECT * FROM @TR

如何将数据插入到 摄氏度 柱?

2 回复 | 直到 6 年前

John Cappelletti 6 年前

也许是这样的。

SOURCETABLE ,我们只需添加一个 UNION ALL ID的可能组合 NULL 价值观。在本例中,MIN(ID)和值1-50

一定要去

1) 用列定义@TR Operator1,OC1,..,Operator50,OC50 <<< OC# 可以是INT

FOR Item IN(Operator1,OC1,..,Operator50,OC50)

示例--修改编辑以允许>9个操作员

INSERT @TR
SELECT *
FROM  (
        Select A.ID
              ,B.*
         From  @OperatorPrice A
         Cross Apply ( values (FName,Price)
                             ,('OC'+replace(FName,'Operator',''),OperatorID)
                     ) B (Item,Value)
        Union All
        Select ID=(select min(ID) From @OperatorPrice)
              ,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 (Operator1, OC1, Operator2, OC2,  Operator3, OC3, Operator4, OC4,  Operator5, OC5) ) AS PivotTable

Select * from  @TR

返回—通知运算符2

Sean Lange 6 年前

select op.ID
    , Operator1 = max(case when convert(int, replace(FName, 'Operator', '')) = 1 then Price end)
    , OC1 = max(case when convert(int, replace(FName, 'Operator', '')) = 1 then OperatorID end)
    , Operator2 = max(case when convert(int, replace(FName, 'Operator', '')) = 2 then Price end)
    , OC2 = max(case when convert(int, replace(FName, 'Operator', '')) = 2 then OperatorID end)
    , Operator3 = max(case when convert(int, replace(FName, 'Operator', '')) = 3 then Price end)
    , OC3 = max(case when convert(int, replace(FName, 'Operator', '')) = 3 then OperatorID end)
    , Operator4 = max(case when convert(int, replace(FName, 'Operator', '')) = 4 then Price end)
    , OC4 = max(case when convert(int, replace(FName, 'Operator', '')) = 4 then OperatorID end)
    , Operator5 = max(case when convert(int, replace(FName, 'Operator', '')) = 5 then Price end)
    , OC5 = max(case when convert(int, replace(FName, 'Operator', '')) = 5 then OperatorID end)
from @OperatorPrice op
cross apply
(
    values
    (1)
    ,(2)
    ,(3)
    ,(4)
)x(N)
group by op.ID