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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-15 : 10:28:37
|
| sree writes "Hi I have a question..How can I create a temporary table that contains columns that change constantly. In other words I would not know how many columns the table has before the query executes. The columns of the tables would be the week names between 2 dates. The input to the procedure is 2 date fields. The difference in weeks between the dates should be the number of columns. I am trying like this.. Any thoughts...?? Thanks in anticipation of your helpThanksSree--------------------DECLARE @ToDt Datetime, @FromDt Datetime, @DateDiff Int,SET @FromDt = '05/09/2002'SET @ToDt = '06/12/2002'SELECT @WeekDay = DATEPART(WeekDay,@FromDt)-- DateDiff gives the number of DATEPART boundaries crossed-- from start date to end date.SELECT @DateDiff = DATEDIFF(Week,@FromDt, @ToDt) SET @TableCount = @DateDiff-- CREATE AND DROP A TEMPORARY TABLE DYNAMICALLYDROP TABLE #ChartsGOCREATE TABLE #Charts( FDate DATETIME,WHILE (@TableCount >= 1)BEGIN 'Week' + CAST(@TableCount AS VARCHAR(3)) Varchar(20), SET @TableCount = (@TableCount - 1)END)" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 11:35:28
|
| Well, I would recommend against storing the data that way. Instead, store each week value on a separate row, using a table like this:CREATE TABLE WeekData (weeknumber int, col1 varchar(20), col2 varchar(50) )Just add the additional columns you need to the definition. In fact, you don't even need a temp table; once you set this up you can use it for all of these type of operations. Just insert, use, and then delete the rows as needed.You can use this technique:http://www.sqlteam.com/item.asp?ItemID=3332...to generate the week numbers/date ranges you're working with based on the date inputs.If you need to cross-tab the results (one date per column) check these out:http://www.sqlteam.com/SearchResults.asp?SearchTerms=cross+tab |
 |
|
|
|
|
|
|
|
Creating Dynamic Tables...