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().

## No comments:

## Post a Comment