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 |
|
Clages1
Yak Posting Veteran
69 Posts |
 Posted - 2007-01-03 : 15:59:08
|
| hii have a table named moviment like thisStore - Goods - Invoice - date - qty - etc01 123 4567 xxxx 1001 123 6545 xxxx 1504 123 66464 xxxx 2009 344 4542 xxxx 33....I can have up to 300 stores cross the countryi would like to create a New table named SALDO like thissince the user can choose how many stores he wants.if he choose 3 the table should be 3 columnsif he choose 44 the table should be 44 columnsand so onGoods Store01 Store02 Store03 Store04 .... store09 storeNN123 25 0 0 20 0344 0 0 0 0 33as i can have more than 300 stores and i would not like to create a table with 300 columnswhat is the best solutions to create only the columns (stores) that have balance <> 0 ?tksC. lages |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-03 : 16:15:03
|
| You mustn't create a table with a dynamic number of columns. What is the table for? If you just want a list of balances for a selected number of stores, write a query like thisSELECT Goods, Store, sum(qty) AS SALDOFROM movimentGROUP BY Goods, StoreIf you really want a copy of the data in another table (and I'm pretty sure you don't because then you have to keep it updated whenever the source data changes), then create a table with a similar structure to the query I gave you (columns for Goods, Store and Saldo).If you want to report that data with stores as columns then you can either do that in a reporting tool like SQL Server Reporting Services or Crystal Reports, or you can write a cross-tab query. For cross-tab queries, you can use the PIVOT clause of the SELECT statement on SQL Server 2005, or see here for a way to do it on SQL Server 2000http://sqlteam.com/item.asp?ItemID=2955 |
 |
|
|
|
|
|
table with a dynamic number of columns