How can I rotate the results of a query in SQL Ser

问题:

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

回答1:

You can try PIVOT

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