How to dynamically pivot data using SQL

SELECT CAST(1 as Int) AS RowNum, CAST(5 as Int) AS RowValue

INTO #tmp;

INSERT INTO #tmp VALUES(2,7);

INSERT INTO #tmp VALUES(3,4);

INSERT INTO #tmp VALUES(4,5);

INSERT INTO #tmp VALUES(5,3);

INSERT INTO #tmp VALUES(6,6);

INSERT INTO #tmp VALUES(7,7);

INSERT INTO #tmp VALUES(8,9);

INSERT INTO #tmp VALUES(9,1);

INSERT INTO #tmp VALUES(10,2);

INSERT INTO #tmp VALUES(11,8);

INSERT INTO #tmp VALUES(12,3);

-------------------------------------------------

-- The following statement creates the field list

-- For each row of the source data

-- the output variable is concatenated

-- Very interesting!

-------------------------------------------------

DECLARE @PivotColumnHeaders VARCHAR(MAX);

SELECT @PivotColumnHeaders =

COALESCE(

@PivotColumnHeaders + ',[' + cast(RowNum as varchar) + ']',

'[' + cast(RowNum as varchar)+ ']'

)

FROM #tmp

ORDER BY RowNum;

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'SELECT * FROM #tmp

PIVOT

(

SUM(RowValue) FOR RowNum IN (' + @PivotColumnHeaders + ')

) AS Pvt'

EXECUTE(@sql)