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

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

click here for SQL Server Execution plan for Unpivot

From the execution plan, I noticed that SQL Server uses constant scan operator 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 SQL Server has 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;

Result:

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