select a set of values as a column without CREATE

Question

I'm trying to write a query that will return all QUERY_ID values alongside all matching TABLE_ID values, where QUERY_ID is not specified in any table, and I can't create tables, so have to specify it in the query itself:

QUERY_ID    TABLE_ID
1           1
2           NULL
3           3
4           4
5           NULL

I feel like there ought to be a simple way to do this, but I can't think of it for the life of me. Any help would be wonderful. Thanks!

Answer 1

select q.QUERY_ID, t.TABLE_ID
from (
    select 1 as QUERY_ID
    union all
    select 2 
    union all
    select 3 
    union all
    select 4 
    union all
    select 5 
) q 
left outer join MyTable t on q.QUERY_ID = t.TABLE_ID

Answer 2

one way by using the built in master..spt_values table

SELECT number AS QUERY_ID,TABLE_ID
FROM master..spt_values v
LEFT JOIN YourTable y ON  y.QUERY_ID = y.TABLE_ID
WHERE TYPE = 'p'
AND number > 0
AND number <= (SELECT COUNT(*) FROM YourTable)
order by QUERY_ID

are you able to create #temp tables...can you do this?

create table #temp(QUERY_ID int identity,TABLE_ID varchar(200))

insert #temp(TABLE_ID)
select TABLE_ID
from YourTable

select * from #temp
order by QUERY_ID

drop table #temp

or like this

select identity(int,1,1) as QUERY_ID,TABLE_ID
into #temp
from YourTable

select * from #temp
order by QUERY_ID

On sql server 2005 and up there is the row_number function so maybe a reason to upgrade :-)