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]

Solution 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

Solution 2

I think you'll find the answer in this answer to a slightly different question: Generate "scatter plot" result of members against sets from SQL query

The answer uses Dynamic SQL. Check out the last link in mellamokb's answer: http://www.sqlfiddle.com/#!3/c136d/14 where he creates column names from row data.

Solution 3

In case you have a grouped flat data structure that you want to group transpose, like such:

GRP  | ID
---------------
1    | 1234
1    | 1235
1    | 1236
1    | 1267
1    | 1278
2    | 1234
2    | 1235
2    | 1267
2    | 1289

And you want its group transposition to appear like:

GRP | Column 1 | Column 2 | Column 3 | Column 4 | Column 5
-------------------------------------------------------------
1   | 1234     | 1235     | 1236     | 1267     | 1278
2   | 1234     | 1235     | NULL     | 1267     | NULL

You can accomplish it with a query like this:

SELECT
    Column1.ID As column1,
    Column2.ID AS column2,
    Column3.ID AS column3,
    Column4.ID AS column4,
    Column5.ID AS column5
FROM
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1234) AS Column1
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1235) AS Column2
ON Column1.GRP = Column2.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1236) AS Column3
ON Column1.GRP = Column3.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1267) AS Column4
ON Column1.GRP = Column4.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1278) AS Column5
ON Column1.GRP = Column5.GRP

(1) This assumes you know ahead of time which columns you will want — notice that I intentionally left out ID = 1289 from this example

(2) This basically uses a bunch of left outer joins to append 1 column at a time, thus creating the transposition. The left outer joins (rather than inner joins) allow for some columns to be null if they don't have corresponding values from the flat table, without affecting any subsequent columns.