T-SQL - Create temp table with dynamic columns

In this post, let us see how to create temp table with dynamic columns.

DECLARE @ColumnsList TABLE ([DBId] INT,Versions VARCHAR(50))

INSERT @ColumnsList SELECT 1,'SQL Server 2008'
INSERT @ColumnsList SELECT 1,'SQL Server 2012'
INSERT @ColumnsList SELECT 2,'ORACLE 10g'
INSERT @ColumnsList SELECT 2,'ORACLE 11g'

IF OBJECT_ID('tempdb..##temp') IS NOT NULL
DROP TABLE ##temp
CREATE TABLE ##temp(dummy BIT);

DECLARE @Script         VARCHAR(8000),        
        @Script_prepare VARCHAR(8000);

SET @Script_prepare = 'ALTER TABLE ##temp ADD [?] VARCHAR(100);'
SET @Script = ''
SELECT @Script = @Script + REPLACE(@Script_prepare, '?',Versions) FROM @ColumnsList
WHERE [DBId] = 1 -- to be changed
EXEC (@Script)

ALTER TABLE ##temp DROP COLUMN dummy;

SELECT * FROM ##temp
--DROP TABLE ##temp

Reference - http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/701d7f4a-006a-47ea-9a38-d6417fde5d0b/

See Also:

• My posts on T-SQL