CODEWITHSUNDEEP
sqlsql-server
kaysush
kaysush

Reputation: 4836

Transpose a single row multiple columns into multiple rows single colum

I have a table valued function in sql server which returns multiple rows and single column such as below

1
2
3

I use the syntax select * from dbo.function to use the values returned by this function in where clause of my queries.

Now apart from the value returned by the function I want to put certain hard coded values in that where clause.

For example : 

Select * from dbo.table where ID in (Select * from dbo.function + **I want to add some more values here**)

So that if function returns

1
2
3

I want to add lets say 

4
5

in that list such that final query becomes as follows : 

select * from dbo.table where ID in (1,2,3,4,5)

Upvotes: 0

Views: 245

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Use or:

Select *
from dbo.table
where ID in (Select * from dbo.function) or
      ID in (4, 5)

Although you could mangle the subquery using union all, the above makes the query easier to follow (in my opinion). Also, in the event that "function" is really a table, it is easier for the optimizer to recognize appropriate indexes.

Upvotes: 2

Related Questions