Not able to use temporary column created using Cross apply in Left Join SQL

Question

I have used Cross apply to Flip data from Wide to Tall format. Used answer from following question to implement Using PIVOT to Flip Data from Wide to Tall

I have created temporary column Sector. I want to use this column for Left Join with another table. But i am getting error Invalid column name 'Sector' on this line V.Sector = S.[Sector name]

SELECT Date,
      Country,
      Sector,
      PE,
      PX_BOOK
      S.Sector_level
FROM [Economic_Data].[dbo].[Sector_Valuations] V
CROSS APPLY 
(
  VALUES
    ('Large Cap Equity',[Large Cap Equity_PE],[Large Cap Equity_book]),
    ('Mid Cap Equity',[Mid Cap Equity_PE],[Mid Cap Equity_book]),
    ('Small Cap Equity',[Small Cap Equity_PE],[Small Cap Equity_book]),
    ('Value Index',[Value Index_PE],[Value Index_book]),
    ('Growth Index',[Growth Index_PE],[Growth Index_book]),
    )  x (Sector, PE, PX_BOOK)  
 Left join [Economic_Data].[dbo].[Sector level] S
 on  V.Sector = S.[Sector name] 

Can anybody help me to fix this issue. Thanks!

Answer 1

You need to refer to the cross apply alias:

[Economic_Data].[dbo].[Sector level] S
on V.Sector = x.[Sector name] 
--------------^

Answer 2

Its looks like you need remove the last comma.

SELECT Date,
      Country,
      Sector,
      PE,
      PX_BOOK
      S.Sector_level
FROM [Economic_Data].[dbo].[Sector_Valuations] V
CROSS APPLY 
(
  VALUES
    ('Large Cap Equity',[Large Cap Equity_PE],[Large Cap Equity_book]),
    ('Mid Cap Equity',[Mid Cap Equity_PE],[Mid Cap Equity_book]),
    ('Small Cap Equity',[Small Cap Equity_PE],[Small Cap Equity_book]),
    ('Value Index',[Value Index_PE],[Value Index_book]),
    ('Growth Index',[Growth Index_PE],[Growth Index_book])--, <------
    )  x (Sector, PE, PX_BOOK)  
 Left join [Economic_Data].[dbo].[Sector level] S
 on  V.Sector = S.[Sector name]