Dynamically Create columns

Based on the value of a variable I want to be able to create columns
dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
creates 5 columns. Could I generate the T-SQL statements, store it in a
variable and pass the value of the variable for achieving this?
Many thanks
ShahriarDynamic SQL - http://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:MWQ5e.6009$9i7.3961@.trnddc04...
> Based on the value of a variable I want to be able to create columns
> dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
> creates 5 columns. Could I generate the T-SQL statements, store it in a
> variable and pass the value of the variable for achieving this?
> Many thanks
> Shahriar
>|||In general that's a really bad idea. Please explain why you want to do this
because I expect there is a better way.
In a well-designed application, the columns in a table should be static. To
add columns dynamically in TSQL you will need to use dynamic code, again
something that you should avoid for lots of good reasons. See the following
article:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||Hmmm...I do not understand your question completely but you can do this:
if @.n=5
select col1,col2,col3,col4,col5
from MyTable
else if @.n=6
select col1,col2,col3,col6,col7
from MyTable
else
...etc etc
OR
declare @.sqlString vachar(255),@.colNames varchar(255);
set @.sqlString = '';
set @.colNames = '';
if @.n=5
set @.colNames= ' col1,col2,col3,col4,col5 ';
else if @.n=6
set @.colNames= ' col1,col2,col3,col6,col7 ';
else
...etc etc
set @.sqlString='select '+colNames+' from MyTable';
--for non parametrized dynamic query
exec @.sqlString;
OR
--for parametrized dynamic query and better performance
sp_executesql @.sqlString
Anyway it heavily depends what you actually want to accomplish. And inline
sql query is always better solution then dynamic.
Regards,
Marko Simic
"Shahriar" wrote:

> Based on the value of a variable I want to be able to create columns
> dynamically. i.e. if @.n=5, I want to generate T-SQL statements that
> creates 5 columns. Could I generate the T-SQL statements, store it in a
> variable and pass the value of the variable for achieving this?
> Many thanks
> Shahriar
>
>|||David
How would you suggest I should tackle this problem without being able to
create Columns Dynamically.
Lets say I have the following table:
Name Car YearBought
John BMW 2004
John FORD 2003
Mary LEXUS 2001
Harry FORD 1999
Harry BMW 2000
Harry VW 2002
Henry JEEP 2004
I want a new table that looks like this:
BMW FORD JEEP LEXUS VW
John 2004 2003
Mary 2001
Harry 2000 1999 2002
Henry 2004
Please note the sort order of columns and also be able to accomodate
inserting a person that drives a car that has not been defined previously.
i.e... the following record gets inserted (Henry JAGUAR 2005 ), as a
result my new table should be:
BMW FORD JAGUAR JEEP LEXUS VW
...
...
...
By being able to dynamically create columns, I could program this quite
easily. Any suggestion(s) is much appreciated.
Thanks
Shahriar
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:VZqdnTgfX9DqQMrfRVn-1Q@.giganews.com...
> In general that's a really bad idea. Please explain why you want to do
> this because I expect there is a better way.
> In a well-designed application, the columns in a table should be static.
> To add columns dynamically in TSQL you will need to use dynamic code,
> again something that you should avoid for lots of good reasons. See the
> following article:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>|||The answer is that you just DON'T do this. Such a table is a violation of
Normalization and other basic database design principles. So I repeat my
original question: Why would you want to?
If you just want a report, that's a different matter. A report isn't a
table. This is called a Cross Tab report and most reporting tools will
generate it automatically for you.
David Portas
SQL Server MVP
--|||Simic
Thank you. You put me in the right direction. This is what I wanted to do.
I can change the @.mysql statement and exec will take care of it. Thanks.
declare @.mysql varchar (255)
Set @.mysql='Create table x (col1 int)';
exec(@.mysql)
Shahriar
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:B79F7D5F-D6EE-426F-B963-46D1FA50842E@.microsoft.com...
> Hmmm...I do not understand your question completely but you can do this:
> if @.n=5
> select col1,col2,col3,col4,col5
> from MyTable
> else if @.n=6
> select col1,col2,col3,col6,col7
> from MyTable
> else
> ...etc etc
> OR
> declare @.sqlString vachar(255),@.colNames varchar(255);
> set @.sqlString = '';
> set @.colNames = '';
> if @.n=5
> set @.colNames= ' col1,col2,col3,col4,col5 ';
> else if @.n=6
> set @.colNames= ' col1,col2,col3,col6,col7 ';
> else
> ...etc etc
> set @.sqlString='select '+colNames+' from MyTable';
> --for non parametrized dynamic query
> exec @.sqlString;
> OR
> --for parametrized dynamic query and better performance
> sp_executesql @.sqlString
> Anyway it heavily depends what you actually want to accomplish. And inline
> sql query is always better solution then dynamic.
> Regards,
> Marko Simic
> "Shahriar" wrote:
>|||David
I am not sure why you are so persistent in this. What am I violating? I
want to create a STAND ALONE table. Normalization comes into picture if
this table will somehow relate to another table. In this case, it does not.
Purely a stand alone table. What I was looking for was something like this:
declare @.mysql varchar (255)
Set @.mysql='Create table x (col1 int)';
exec(@.mysql)
By changing my mysql statement, I can achieve what I want to accomplish.
Here is a nice sample use of the above application.
Lets say I want to create the sample table I posted earlier on a monthly
basis on a web site. I will run my application to generate the new table
and I am done. Solving this the way I did, has nothing to do with
normalization!
Regards
Shahriar
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:lfmdnZ8-ruDceMrfRVn-2g@.giganews.com...
> The answer is that you just DON'T do this. Such a table is a violation of
> Normalization and other basic database design principles. So I repeat my
> original question: Why would you want to?
> If you just want a report, that's a different matter. A report isn't a
> table. This is called a Cross Tab report and most reporting tools will
> generate it automatically for you.
> --
> David Portas
> SQL Server MVP
> --
>|||These articles may help you bit more:
http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlteam.com/item.asp?ItemID=5741
http://www.umachandar.com/technical...cripts/main.htm
Regards,
Marko Simic
"Shahriar" wrote:

> Simic
> Thank you. You put me in the right direction. This is what I wanted to d
o.
> I can change the @.mysql statement and exec will take care of it. Thanks.
> declare @.mysql varchar (255)
> Set @.mysql='Create table x (col1 int)';
> exec(@.mysql)
> Shahriar
> "Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
> news:B79F7D5F-D6EE-426F-B963-46D1FA50842E@.microsoft.com...
>
>|||No it wont.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:aZydnWgKIPm9n8XfRVn-3w@.giganews.com...
> Check out Reporting Services:
> http://www.microsoft.com/sql/reporting/default.asp
> It will do all you want without the maintenance and security implications
> of Dynamic SQL. It's also a more scalable approach than duplicating the
> data for every report in a table.
> --
> David Portas
> SQL Server MVP
> --
>