Convert rows to columns - PIVOT

Suppose there is a table having multiple rows with a single column and you want to convert the data to a row with multiple corresponding columns. For example, you have the following table A that has only one column (col). The table A has 4 rows. Now let's say you want to convert it to a single row with 4 columns.

CREATE TABLE A(col int)
GO
insert A values (10)
insert A values (20)
insert A values (30)
insert A values (40)
GO

This conversion can be done by using PIVOT. To make things easy, we can add another computed column (r) that has row number. First, we select the single column (col) and computed row number from source table. For each row, PIVOT statement asks to calcualte SUM of col, which basically the same col value in this case since there is no aggregation here. If column type is non-numeric value such as string type, other aggregate function such as MIN() can be used. So from those four rows, the pivot returns 4 column data.

SELECT [1],[2],[3],[4]
FROM (SELECT col, row_number() over (order by col) r
      FROM A) AS sourceTable
PIVOT
(
   SUM(col) FOR r IN ([1],[2],[3],[4])
) AS p
GO

The result is as follows.



If rows are unknown, we can use dynamic pivot by adding the corresponding number of columns in IN clause and use SELECT * instead of SELECT [1],[2],[3],[4]. And the dynamic SQL will be run by using EXECUTE().