Select values from same column from same table

I've a column Question_Id from which I've to derive two sets of values based on a different column in SQL Server 2008.

Question_id and ID

• 243 73
• 244 73
• 245 73
• 429 192
• 430 192
• 431 192

How can I get them like this: (This is only for temporary table)

• 1 243 429
• 2 243 430
• 3 243 431

Thanks a lot in advance开发者_Go百科. Manish

---

Thanks for your replies. For those who weren't clear about the question - I want the result to be like that temporary table. Yesterday I realized that, Actually its not possible to select different values from same column.... I used two temporary tables SELECT QUESTION_ID WHERE ID = 73 INTO #TEMP1 and SELECT QUESTION_ID WHERE ID = 192 INTO #TEMP2. Then I inner-joined both tables to use the data for some INSERT operation... Thank you.

---

You cannot. There is no natural ordering in SQL databases, so without the 1, 2, 3 values in the original rows, there's no way to pair up, for instance, 243 and 429.

---

If you setup a cross join with something like:

SELECT t1.qid, t2.qid FROM table as t1, table as t2 WHERE t1.qid=243 AND t2.qid between 429 AND 431;

And insert that into a new table with an AI index, you could create something like your description.