Convert single column to single row

Question

I have a table with a single column like so:

+-------------+
|(Column Name)|
+-------------+
|Data1        |
+-------------+
|Data2        |
+-------------+
|Data3        |
+-------------+
|Data4        |
+-------------+
|Data5        |
+-------------+

What I want to do seems very simple, but I am not able to find any examples of it anywhere. All I want is to convert the above column into a single row like so:

+-------+-------+-------+-------+-------+
| (Col1)| (Col2)| (Col3)| (Col4)| (Col5)|
+-------+-------+-------+-------+-------+
| Data1 | Data2 | Data3 | Data4 | Data5 |
+-------+-------+-------+-------+-------+

I'm sure this is a very simple task, but I am extremely new to working with databases. I appreciate any help.

Answer 1

You can do pivot as below:

Select * from (
    Select colname, RowN = Row_Number() over (order by colname) from #cols
    ) a
    pivot (max(colname) for RowN in ([1],[2],[3],[4],[5])) p

For dynamic list of columns

Declare @cols nvarchar(max)
Declare @query nvarchar(max)

Select @cols = stuff((select ','+QuoteName(Row_Number() over (Order by (Select NULL))) from #cols for xml path('')),1,1,'')
Select @query = ' Select * from (
    Select colname, RowN = Row_Number() over (order by colname) from #cols
    ) a
    pivot (max(colname) for RowN in (' + @cols + ')) p '

Exec sp_executesql @query