CODEWITHSUNDEEP
sql-server-2008t-sqlsubquery
Joe Bank
Joe Bank

Reputation: 663

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

Upvotes: 0

Views: 2248

Answers (2)

Amir.F
Amir.F

Reputation: 1961

doesn't this work?

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

or do you need something else?

Upvotes: 0

Jānis
Jānis

Reputation: 2266

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)

Upvotes: 4

Related Questions