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.
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
Pivot Query:
CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)
INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)
SELECT * FROM SALES
Example:
SELECT [January]
, [February]
, [March]
FROM ( SELECT [Month]
, SaleAmount
FROM Sales
) p PIVOT ( SUM(SaleAmount)
FOR [Month]
IN ([January],[February],[March])
) AS pvt
