Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2011-03-17 : 15:15:54
|
| hi folks, is there a way (which should be) to create a table with N number of columns having datatype as Varchar? e.g. If @N = 2 then create table having two columns (Col1,col2) If @N= 10 Then create table having ten columns (Col1,col2,...,Col10)Value of N is determined via an integer column of a table at run time in a stored procedure.CheersMIK |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 15:23:11
|
quote:
is there a way (which should be)
There is, but the point is you SHOULDN'T do that. Better to use a structure like this:CREATE TABLE test (ID int not null identity(1,1) primary key,Col int not null, -- the column "number" would go hereValue varchar(30))If you need to reformat to what you described you can always use PIVOT or CASE expressions. |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 15:37:43
|
| CREATE TABLE test (ID int not null identity(1,1) primary key,Col int not null, -- the column "number" would go hereValue varchar(30))Replacing "the column "Number" would go here" with the column Number which is 10, goes for syntax error Msg 102, Level 15, State 1, Line 2Incorrect syntax near '10'.Rob, please explain the point you emphasized ... "Shouldn't do that" Why please? Also, PIVOT or CASE is not the solution what i am looking for. I need to store the result set of a procedure, whose output is not of fixed number of columns.CheersMIK |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 15:41:12
|
| Ugh, never mind then.Basically my structure would "unpivot" the data, that's the optimal way to store it. If you read up on UNPIVOT in Books Online you'll see how it works. Since you can't control the output this won't be an option for you."Shouldn't do that" refers to using multiple columns to store the same data or data type, like Phone1, Phone2, Phone3 or Address1, Address2, etc. Since you're not doing that the argument is moot. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-17 : 16:10:59
|
| That will work, it's as good an approach as any. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-18 : 00:41:49
|
| Many thanks!!CheersMIK |
|
|
|
|
|
|
|
|
create table with N Columns