[T-SQL]類似Excel的VLOOKUP(或EEP的RefVal效果)

類似Excel的VLOOKUP (或是有人用find + index + match拼起來)

大致上的思路是：

1. 利用子查詢來產生參照後的值
2. 由於我是用一個Base的Table放所有的List, 所以還要再加上WHERE篩分類
3. 將兩者LEFT JOIN
4. SELECT不顯示索引而是選擇參照過去的值

↓上排是before, 下排是after。


這方法會產生大量的子查詢, 請視需求使用
應該有更好的方法, 只是T-SQL的語法待精進

---

【原本】
SELECT  
Design.PNo, Design.DesignVer
      ,[Drawal1]
      ,[Drawal2]
(中略)
      ,[Drawaln]
FROM  dbo.eCRF_Design
left JOIN (SELECT PNo,DesignVer ,[State] FROM eCRFProj) as ProjDesc ON ProjDesc.PNo = eCRF_Design.PNo AND ProjDesc.DesignVer=eCRF_Design.DesignVer

【模擬RefVal】
SELECT  
Design.PNo, Design.DesignVer,
D1.Desc_en AS w1,
D2.Desc_en AS w2,
(中略)
Dn.Desc_en AS wn

FROM         Design
left JOIN (SELECT PNo,DesignVer ,[State] FROM Proj) as ProjDesc ON ProjDesc.PNo = Design.PNo AND ProjDesc.DesignVer=Design.DesignVer
left JOIN (SELECT Code, Desc_en FROM Base AS Base WHERE (Category = 'Design_drawal')) AS D1 ON D1.Code = Design.Drawal1
left JOIN (SELECT Code, Desc_en FROM Base AS Base WHERE (Category = 'Design_drawal')) AS D2 ON D2.Code = Design.Drawal2
(中略)
left JOIN (SELECT Code, Desc_en FROM Base AS Base WHERE (Category = 'Design_drawal')) AS Dn ON Dn.Code = Design.Drawaln