可以使用xml将查询的所有结果存储到列中

CUSIP=(Select gs.holdingsymbol 
           from dbo.holdingsymbol_tbl gs where gs.holdingsymboltypeid=2 for xml path(''))

您不需要子查询，只需在JOIN上添加另一个条件，如下所示：

SELECT DISTINCT
    h.tradingitemid
    ,h.securityid
    ,h.currencyId
    ,CUSIP = hs.holdingsymbol
FROM cpr..holding_tbl h
INNER JOIN cpr..HoldingSymbol_tbl hs ON h.holdingid = hs.holdingId AND hs.holdingsymboltypeid = 2
WHERE h.userCompanyId = 10;

您可以尝试这样做：

CUSIP=(Select TOP 1 gs.holdingsymbol 
           from dbo.holdingsymbol_tbl gs where gs.holdingsymboltypeid=2)