Create table with number of columns in a parameter

Question

Create table with number of columns in a parameter

nicolas.pages

SSC-Addicted

Points: 430
More actions
March 10, 2008 at 4:23 am

#71710

Hi,
I am wondering if it is possible to create a temp table with a parameter that would contain the number of columns of the temp table. All the columns would of course be of the same type, and so there'd be a loop that would look like :
declare @counter int
set @counter = 1
Create table #Result
(
while @counter < @param
begin
if @counter = @param
begin
'Week'+@counter int null
end
else
begin
'Week'+@counter int null,
end
select @counter=@counter+1
end
)
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

nicolas.pages (3/10/2008)
Hi,
I am wondering if it is possible to create a temp table with a parameter that would contain the number of columns of the temp table. All the columns would of course be of the same type, and so there'd be a loop that would look like :
declare @counter int
set @counter = 1
Create table #Result
(
while @counter < @param
begin
if @counter = @param
begin
'Week'+@counter int null
end
else
begin
'Week'+@counter int null,
end
select @counter=@counter+1
end
)
Thanks!

While it is possible to do this (e.g. using dynamic SQL), there are much better solutions that would be easier to write, would perform better, and will be easier to maintain. Using rows to store the above information together with something to identify the week is likely to be a better idea. Could you describe what you are trying to do?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 2

Hi and thanks for your answer. I am actually trying to create a report using SQL Reporting Services Server, that would take as parameters the begin date and end date. The records to be displayed are events that may last from one day to a few months. I need to base the selection on the dates, among other things.

The report must look like a timeline with each line being an event and with the weeks as columns. Im not quite sure yet that this is feasible in a nice manner, but I m looking into it. I still need to figure out if it is possible to have a variable number of columns in a report... Would you happen to know the answer to that question? : )

Thank you!

Nicolas

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 3

nicolas.pages (3/10/2008)
Hi and thanks for your answer. I am actually trying to create a report using SQL Reporting Services Server, that would take as parameters the begin date and end date. The records to be displayed are events that may last from one day to a few months. I need to base the selection on the dates, among other things.
The report must look like a timeline with each line being an event and with the weeks as columns. Im not quite sure yet that this is feasible in a nice manner, but I m looking into it. I still need to figure out if it is possible to have a variable number of columns in a report... Would you happen to know the answer to that question? : )
Thank you!
Nicolas

You may want to look at PIVOTS with dynamic columns (I assume you are running SQL Server 2005). An example is on my blog http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

nicolas.pages SSC-Addicted Points: 430 More actions · Answer 4

All right, thanks for that answer. The only thing is I am using sql server 2000, so i need to look if pivoting tables is possible there, but i assume it has to be the case.

Regards,

Nicolas

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

nicolas.pages (3/10/2008)
All right, thanks for that answer. The only thing is I am using sql server 2000, so i need to look if pivoting tables is possible there, but i assume it has to be the case.
Regards,
Nicolas

You should post SQL Server 2000 questions in the SQL Server 2000 forums... you'll get answers for SQL Server 2000 the first time, that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 6

nicolas.pages (3/10/2008)
All right, thanks for that answer. The only thing is I am using sql server 2000, so i need to look if pivoting tables is possible there, but i assume it has to be the case.
Regards,
Nicolas

Well, on SS 2000 if you want to do pivot with dynamic columns you will need to write much more code 🙁 An example on how to do pivot (without the dynamic columns) on 2000 is on http://www.dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 734459

March 10, 2008 at 8:36 am

#787392

Moved to 2000

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Read topic "Cross-Tab Reports" in BOL

_____________
Code for TallyGenerator