How can I rotate the results of a query in SQL Server 2005?

Question

I would like to be able to rotate the results of a SQL Query so that instead of the results being returned in a series of rows the results are returned as a single row with multiple columns. For example with the following table.

Create Table TestData 
(
things varchar(25)
)

Insert into TestData values ('Thing1') 
Insert into TestData values ('Thing2')
Insert into TestData values ('Thing3')
Insert into TestData values ('Thing4')

I would like a select statement like 'Select things from TestData'

to return something like Thing1 Thing2 Thing3 Thing4

rather than

1. Thing1
2. Thing2
3. Thing3
4. Thing4

Thanks in advance for the help.

Update:

After seeing the recommendation by Gratzy to use Pivot I found I can get the desired result by adding an identity column to the table like so.

Create Table TestData 
(
id int identity,
    things varchar(25)
)

and then running the following. SELECT * FROM TestData PIVOT ( MAX(Things) FOR [ID] IN ([1],[2],[3],[4]) ) AS Result

Answer 1

You can try PIVOT

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 2

This might be interesting for you

LINK

Same Problemm, different solutions.

Answer 3

Hello,

Take a look at this link :

Pivot SQL 2005/2008

Might be helpful ;)