Dynamic columns in table

 USE TEMPDB

GO

 

CREATE TABLE data (

                [Key] VARCHAR(100) ,

                [Value] VARCHAR(100) 

);

 

INSERT INTO data VALUES('X1','1');

INSERT INTO data VALUES('X3','2');

INSERT INTO data VALUES('X5','3');

INSERT INTO data VALUES('Y1','10');

INSERT INTO data VALUES('Y2','20');

INSERT INTO data VALUES('Y3','30');

 

Select * from data;

CREATE TABLE #temp1(dummy BIT);

 

DECLARE @Script1 AS VARCHAR(8000);

DECLARE @Script_prepare1 AS VARCHAR(8000);

 

SET @Script_prepare1 = 'Alter table #temp1 Add [?] varchar(100);'

SET @Script1 = ''

 

SELECT

            @Script1 = @Script1 + Replace(@Script_prepare1, '?', [Value])

FROM

            data

--WHERE

--            [Value] like  'x' 

 

Exec (@Script1)

 

Alter table #temp1 drop column dummy;

 

CREATE TABLE #temp(dummy BIT);

 

DECLARE @Script AS VARCHAR(8000);

DECLARE @Script_prepare AS VARCHAR(8000);

 

SET @Script_prepare = 'Alter table #temp Add [?] varchar(100);'

SET @Script = ''

 

SELECT

            @Script = @Script + Replace(@Script_prepare, '?', [Key])

FROM

            data

--WHERE

--            [Value] like  'x' 

 

Exec (@Script)

 

Alter table #temp drop column dummy;

 

Select * from #temp;

Select * from #temp1; 

--drop table #temp1

--DROP TABLE DATA