how to create dynamic columns in a temporary table

Question

Hi there,
           i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like

Key	Value
X1	x
X3	x
X5	x
Y1	y
Y2	y

when user select x, the input variable passed to stored proc and the result is shown like

column names
 X1      X3    X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

Answer 1

Here the sample script,

 

 

 

Code Snippet

 

use tempdb

go

 

Create Table data (

                [Key] Varchar(100) ,

                [Value] Varchar(100) 

);

 

Insert Into data Values('X1','x');

Insert Into data Values('X3','x');

Insert Into data Values('X5','x');

Insert Into data Values('Y1','y');

Insert Into data Values('Y2','y');

 

 

 

 

Code Snippet

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] = 'X'

 

Exec (@Script)

 

Alter table #temp drop column dummy;

 

Select * from #temp;

 

drop table #temp

 

 

 

Answer 2

Hi sekaran,
very nice and thanks alot.

but, i don't know that [key]. it's not static.
it is based on the result from a select query.
how to replace that '?' with the key, i need to write some other logic to get that key...

thanks
PRaveen.

Answer 3

Can you post more information  like result query & etc.?

 

Answer 4

Hi Dude,

Dear Manivannan

I Appriciate your idea to dynamically adding colums to a temperary table

in general we use Temperary Tables in Stored Procedures or SQL Function

once we have dynamically changed the structure of temperary table how do we

insert records into the temperray table using the dynamic column values

your efforts and help is appriciated in advance

Thanks and Regards

JAM

Answer 5

Excellent example! Good work. :)

Answer 6

Manivannan! You gave a very good example which showed us the path to achieve what we want.

---

Pops