Selecting multiple columns from a subquery

问题:

I've searched a lot, but still no chance on having a subquery to return multiple columns all at once. The following code works, but it sucks:

SELECT
    (SELECT Column1 FROM dbo.fnGetItemPath(ib.Id)) AS Col1,
    (SELECT Column2 FROM dbo.fnGetItemPath(ib.Id)) AS Col2,
    (SELECT Column3 FROM dbo.fnGetItemPath(ib.Id)) AS Col3
FROM ItemBase ib

I actually have got no idea how to pass ib.Id to the function and get the entire Column1, Column2, Column3 columns without calling the fnGetItemPath function 3 times.

Thanks in advance

回答1:

You can move ti to "FROM" part and use outer apply (or cross apply).

check syntax yourself, but it should look something like this:

SELECT Column1, Column2, Column3
FROM ItemBase ib
Outer Apply dbo.fnGetItemPath(ib.Id)

回答2:

doesn't this work?

select 
     (select column1, column2, column3 from dbo.fnGetItemPath(ib.Id)) 
from ItemBase ib

or do you need something else?