How to pivot by two columns into predefined table?

Question

I've seen this question, this and this, however these are not what I want.

Please, do not close my question as it is not duplicate. It is really important to me.

I've managed to pivot a table, but this is not desired result:

The desired result looks like this:

My sample data is(it is just an example as OperatorX and OC columns should be 50 times):

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
)

Example code:

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

How can I insert data into OC columns?

This is the same question you asked yesterday. And it STILL is not clear what you want. https://stackoverflow.com/questions/52389185/insert-data-into-table-from-dynamic-sql-without-knowing-column-names You would insert into those columns with an insert statement... oddly...just like the one you posted here in your question. But I suspect that isn't what you are asking. Great job posting ddl and sample data but the question just makes no sense. — Sean Lange, Sep 19 '18 at 20:24
@Sean Lange it is not the same. Please, read carefully my question, just do not close my question. Now I can insert data, but data should be consist of two columns! Please, do not close my thread. — StepUp, Sep 19 '18 at 20:27
You will notice that I did NOT downvote this OR mark it to be closed. In fact you did a great job posting data and table structures. I just don't understand the question. — Sean Lange, Sep 19 '18 at 20:28
I see the data going into the other columns but why is Operator3 OC3? Is the string Operator always in the data? That is what is really confusing here. Is there a better way to determine the number of operator? I am thinking that conditional aggregation would be a lot easier here but we need some way to know which operator number is what. — Sean Lange, Sep 19 '18 at 20:29
@StepUp You can do this very easily with conditional aggregation. Give one good reason why the third question you referenced didn't help you: https://stackoverflow.com/a/44030551/1507566 It only takes a little thought to apply it to two columns instead of one. — Tab Alleman, Sep 19 '18 at 20:34

score 1 · Answer 1 · answered Sep 19 '18 at 20:41

Using your sample tables and data this is pretty easy with conditional aggregation. Not really clear though how you determine which operator number is which. Hopefully you have something better than parsing numbers out of the values but who knows.

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

John Cappelletti · Accepted Answer · 2018-09-19T21:18:55.937

1

Perhaps something like this.

In your alias SOURCETABLE, we just add a UNION ALL of possible combinations for an ID with NULL values. In this case the MIN(ID) and values 1 - 50

Just be sure to

1) Define @TR with columns Operator1,OC1,..,Operator50,OC50 <<< OC# can be an INT

2) in the FOR Item IN(Operator1,OC1,..,Operator50,OC50)

Example -- Edit Corrected to allow for >9 operators

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

Returns -- Notice Operator2

edited Sep 19 '18 at 21:18

answered Sep 19 '18 at 20:52

John Cappelletti

79,615
7
44
66

Thank you very much! IT works perfectly! Could you clarify, please, what does `Over (Order By (Select NULL)) From master` it mean? Thanks in advance! – StepUp Sep 20 '18 at 10:09
1

@StepUp Here we are creating an ad-hoc tally or numbers table via row_number(). In this case, the goal to to get a span of numbers between 1 and 50 (hence the top 50). Row_Number() requires an Order By. I use select null ( a constant) for two reasons. 1) it is faster because there is nothing to sort and the optimizer sees it as a constant 2) rather than master... you can use any table of sufficient rows and the order by is NOT linked to a specific field. – John Cappelletti Sep 20 '18 at 10:20
1

@StepUp If you haven't done so yet. Run the individual statements. Start with the Select to 50 N=-... then run the Select that follows the UNION ALL Once again, always happy to help, and I know what you are doing :) ... so not necessary. – John Cappelletti Sep 20 '18 at 10:30
Could you be very kind to see this question? https://stackoverflow.com/questions/52461516/calc-sum-total-of-pivoted-table-by-two-columns-into-predefined-table – StepUp Sep 22 '18 at 22:53

How to pivot by two columns into predefined table?

2 Answers2