how to create dynamic columns in a temporary table

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.

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

|||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.|||Can you post more information like result query & etc.?

How to create a View from temporary table ?

Hi all,
Iam aware that #Temporary tables can not be used in creation of views, but
iam in a situation where in iam needed to do so.
can anybody provide me with a workaround for this, its very urgent....any
help would be highly appreciated.
Thanks in advance,
Kishore> Iam aware that #Temporary tables can not be used in creation of views, but
> iam in a situation where in iam needed to do so.
Can you elaborate? Why do you need a temporary table and a permanent view
of that temporary table? This sounds like buying a house in Florida because
you're going there for spring break.|||Well, you can't do that.
So if that view that you want is something you will be using in all queries,
then select the required rows and insert into another temporary table and
use this assuming you are using a view.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWPH%23AedGHA.3632@.TK2MSFTNGP05.phx.gbl...
> This sounds like buying a house in Florida because
> you're going there for spring break.
Aaron,
One of these days I am going to put together a compilation of these
analogies of yours. You have a particular knack for them.
I'm not being sarcastic at all. You always manage to come up with a
comparison that suits the situation perfectly.|||Hi,
Thanks for your prompt reply.
As asked by you, i will try to elaborate a bit more, please pardon me for
this lengthy explanation as i am in very urgent need of it, any help would b
e
highly appreciated.
My Business need requires that i need to create a table whose columns names
are taken from data contained in different tables, in essence iam creating a
table dynamically, which will have different number of columns in each run.
For efficeincey purposes, my customers earlier implementation uses Temporary
tables.
finally that dynamic #Temptable is filled up with data and a select
statement applied on it returns the needed data in XML format.
Then they came across the SQL Server row size limit of some 8060 bytes,
since each of the column in my dynamic table is of type NVARCHAR(255), at
maximum i can add 15 columns only to it. so instead of having only single
table, i decided to host those columns in multiple tables, i mean 1-15
columns will be added to #temptable-1 then 16-30 columns will be added to
#temptable-2 and so on....
so that problem has been addressed, but the output of the final select
statement that works on the dynamic #Temptable is creating problem...earlie
r
there was only one table, but now there can be 'n' number of tables dependin
g
on the number of columns to be added, so the XML resulting from the select
statement on these 'n' number of dynamic #Temptables is different from the
earlier on when there was only one dynamic #Temptable, but i need to maintai
n
that format due to some other dependencies.
so i thought of creating a single view(since a table can not hold all the
columns resulting from the select statement on those 'n' number of tables)
from all those #Temptables and then generate XML from that single
view...which will be similar in syntax to the earlier case when there was
only a single #Temptable.
Hope this explains...
Thanks & Regards,
Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>|||becuase of the SQL Server 2000 row size limit of 8060 bytes i can put all my
columns taked from other #Temptables into a new #Temptable to be used as a
view as suggested by you, Total size of all my columns exceeds tht limit so
table option is ruled out.
"Omnibuzz" wrote:

> Well, you can't do that.
> So if that view that you want is something you will be using in all querie
s,
> then select the required rows and insert into another temporary table and
> use this assuming you are using a view.|||Mr Aaron,
have you seen my explanation, can you suggest me anything.
-Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>sql