I’m trying to understand the implementation details for UNPIVOT Operator designed by SQLSERVER.

`SELECT product_name, store_location, num_sales  
FROM unpvt_tbl
UNPIVOT 
(
num_sales FOR store_location IN (Central, North, South, West)
) AS upvt;`

`[SQLSERVER Execution plan](https://i.sstatic.net/yrDUJTc0.png)`

From the execution plan, I noticed that MSSQL uses constant scan opearator to generate rows and later Left join these generated rows with the actual table to generate actual Unpivot data.
Can anyone please suggest the Left join query MSSQL have used with Unpivot (In execution plan, predicates for Left join is not mentioned).

My approach to use temp table and Left join is as follows,

To get the unpivot results manually, instead of constant operator, I created a temporary table as below and performed left join with the original table but failing to get the actual results.

original table used for unpivot

`create table unpvt_tbl ("product_name" varchar (100), "Central" int, "North" int,  "South"     int, "West" int);
insert into unpvt_tbl values ('Chair', 218, 437, 376, 130);
insert into unpvt_tbl values ('Couch', 218, 251, NULL, 143);
insert into unpvt_tbl values ('Desk', 120, 226, 136, 134);`

temp table to replace constant scan operator

`create table temptbl ("store_location" varchar (100), "num_sales" int);
insert into temptbl values ('Central', 218);
insert into temptbl values ('Central', 218);
insert into temptbl values ('Central', 120);
insert into temptbl values ('North', 437);
insert into temptbl values ('North', 251);
insert into temptbl values ('North', 226);
insert into temptbl values ('South', 376);
insert into temptbl values ('South', NULL);
insert into temptbl values ('South', 136);
insert into temptbl values ('West', 130);
insert into temptbl values ('West', 143);
insert into temptbl values ('West', 134); `

Expected Unpivot result

Chair Central 218 Chair North 437 Chair South 376 Chair West 130 Couch Central 218 Couch North 251 Couch West 143 Desk Central 120 Desk North 226 Desk South 136 Desk West 134

My Outer join query

`select tbl1.product_name, tbl2.store_location, tbl2.num_sales from unpvt_tbl tbl1 left outer   join temptbl tbl2 on tbl1."Central" = tbl2.num_sales or tbl1."North" = tbl2.num_sales  or tbl1."South" = tbl2.num_sales  or tbl1."West" = tbl2.num_sales;`

Chair Central 218 Chair North 437 Chair South 376 Chair West 130 Chair Central 218 Couch Central 218 Couch Central 218 Couch North 251 Couch West 143 Desk Central 120 Desk North 226 Desk South 136 Desk West 134

Please help to correct my outer join query. Thanks