Wednesday, September 05, 2007 5:29 AMHi 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
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.
Wednesday, September 05, 2007 6:37 AM
Here the sample script,Code Snippet
CreateTable data (
[Key] 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 = ''
@Script = @Script + Replace(@Script_prepare, '?', [Key])
[Value] = 'X'
Alter table #temp drop column dummy;
Select * from #temp;
drop table #temp
Wednesday, September 05, 2007 9:32 AMHi 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...
Wednesday, September 05, 2007 10:35 AMCan you post more information like result query & etc.?
Thursday, April 02, 2009 11:27 PMHi Dude,
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
Wednesday, November 18, 2009 4:58 PMExcellent example! Good work. :)
Friday, December 04, 2009 6:01 PMManivannan! You gave a very good example which showed us the path to achieve what we want.