无法使用使用Cross Apply在左JOIN SQL中创建的临时列

Question

我使用的交叉应用于从宽到高格式的翻转数据。从以下问题中使用的答案可以使用枢轴将数据从宽

翻转到

我创建了临时列Sector。我想将此列与另一个表一起使用。但是我在此行上遇到错误Invalid column name 'Sector' 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] 

任何人都可以帮助我解决这个问题。谢谢！

Answer 1

您需要参考cross apply别名：

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

Answer 2

它看起来需要删除最后一个逗号。

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]