none
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.



    Wednesday, September 05, 2007 5:29 AM

Answers

  • 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

     

     

     

    Wednesday, September 05, 2007 6:37 AM

All replies

  • 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

     

     

     

    Wednesday, September 05, 2007 6:37 AM
  • 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.
    Wednesday, September 05, 2007 9:32 AM
  • Can you post more information  like result query & etc.?

     

    Wednesday, September 05, 2007 10:35 AM
  • 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
    Thursday, April 02, 2009 11:27 PM
  • Excellent example! Good work. :)
    Wednesday, November 18, 2009 4:58 PM
  • Manivannan! You gave a very good example which showed us the path to achieve what we want.

    Pops
    Friday, December 04, 2009 6:01 PM