SQL Server - Transpose rows into columns

Question

I've searched high and low for an answer to this so apologies if it's already answered! I have the following result from a query in SQL 2005:

ID

1234

1235

1236

1267

1278

What I want is

column1|column2|column3|column4|column5
---------------------------------------
1234   |1235   |1236   |1267   |1278

I can't quite get my head around the pivot operator but this looks like it's going to be involved. I can work with there being only 5 rows for now but a bonus would be for it to be dynamic, i.e. can scale to x rows.

EDIT: What I'm ultimately after is assigning the values of each resulting column to variables, e.g.

DECLARE @id1 int, @id2 int, @id3 int, @id4 int, @id5 int

SELECT @id1 = column1, @id2 = column2, @id3 = column3, @id4 = column4, 
@id5 = column5 FROM [transposed_table]

Answer 1

You also need a value field in your query for each id to aggregate on. Then you can do something like this

select [1234], [1235]
from
(
    -- replace code below with your query, e.g. select id, value from table
    select
    id = 1234,
    value = 1
    union
    select
    id = 1235,
    value = 2
) a
pivot
(
  avg(value) for id in ([1234], [1235])
) as pvt