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 |
|
lane0618
Posting Yak Master
134 Posts |
 Posted - 2002-10-10 : 14:11:06
|
| I have a stored procedure that automativally adds columns to a table. I was not aware that there is a maximum number of columns allowable in a table. I recived the following error:[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE failed because column 'new_column' in table 'features' exceeds the maximum of 1024 columns. Is there any way to increase the max columns from 1024 to more?Thanks,Lane |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-10 : 14:17:49
|
| I really am curious why a table would need that many columns. I'm not sure if there is a way to increase the maximum number of columns, but I would look at normalizing your data. any repetive column that could be turned into reference tables?A work around if you actually need 2 tables would be just to split the table into to and maintain a key between the 2.-----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2002-10-10 : 14:37:10
|
| This is what my table looks like. It has a list of feature descritions and their associated ID. The rest of the columns are SKU numbers (h34-Inss, h34-2nss). The number of skus in now well over 1000, breaking the max allowable columns. In the sku columns are yes/no options speciifing whether or not the sku uses the feature description. the entire table is basically a big (too big) matrix. I'm sure there is a better way, but how?theID/ ftrDesc/ h34-1nss/ h34-2nss3 MatchMakers® Accessories Available 1 07 Matching Soap/Lotion Dispenser Avaliable 0 18 Soap/Lotion Dispenser Included 0 010 Fixed Underbody 0 011 Quick Connect Installation 0 112 Quick Connect Side Spray 0 1thanks,Lane |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 14:52:56
|
| CREATE TABLE SKUFeatures (FeatureID int NOT NULL REFERENCES FeatureTable (theID),SKU varchar(20) NOT NULL CONSTRAINT PK_SKUFeature PRIMARY KEY (SKU, FeatureID))You'd then alter the current table structure to drop the SKU columns and store the data in the SKUFeatures table like so:INSERT INTO SKUFeatures VALUES (3, 'h34-1nss')INSERT INTO SKUFeatures VALUES (7, 'h34-2nss')INSERT INTO SKUFeatures VALUES (11, 'h34-2nss')INSERT INTO SKUFeatures VALUES (12, 'h34-2nss')Notice that you DO NOT store a SKU/Feature combination if that SKU does not have that feature (no rows for Soap/Lotion or Fixed Underbody, because those 2 SKU's do not have them)This is infinitely more practical for you to use because you can support an unlimited number of SKU's and features.<edit> to fix original table structure </edit>Edited by - robvolk on 10/10/2002 15:09:05 |
|
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2002-10-10 : 15:57:16
|
| This method makes a LOT more since. Once I have converted the data, who would I query the data in both table so that it looks like this:theID/ ftrDesc/ h34-1nss/ h34-2nss 3 MatchMakers® Accessories Available 1 0 7 Matching Soap/Lotion Dispenser Avaliable 0 1 8 Soap/Lotion Dispenser Included 0 0 10 Fixed Underbody 0 0 11 Quick Connect Installation 0 1 12 Quick Connect Side Spray 0 1 I can take care of the sku names dynamically, but how do I pivot the data?Thanks,Lane |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 16:07:25
|
| You can try modifying this code:http://www.sqlteam.com/item.asp?ItemID=2955But I can tell you right now that you won't be able to pivot 1,000 columns worth of SKU's in its current form. You'll probably max out at around 200 or so.Quite frankly, I can't see any convenience in having to browse a 1,000 column report/query; quite the opposite in fact. What would that kind of structure be needed for, and is it ABSOLUTELY necessary to have it? I can't believe that it's the most convenient or legible way to read the data.Edited by - robvolk on 10/10/2002 16:09:47 |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-10 : 18:46:30
|
| You can try pivoting n columns at a time and dumping the data to the client (ie: excel) in multiple passes.Maybe even joining the pivoted tables.Your best bet is to only pivot on columns that have data, or group items into categories with the same features. |
|
|
|
|
|
|
|
|
Increase Max Columns