Hi

Following my previous question. Here is the implemetation.

I have to design a report which display monthes as columns.
The number of columns in the report depends on the range paramters.
For example : if the Range is from 01/02/05  to 30/06/05 there ara 6 columns
shown.

01/05,02/05.....06/05. (column alias) .
if the range of dates are 8 monthes range there are 8 dynamic columns, etc'

1) My first major problem is to write a SP or a function that return a
dynamic number of columns.
2) Second,(This is not a must) to change column alias (Heading) according to
the parameters.


thanks

What reporting tool do you use? This is called a cross tab report and
any decent reporting tool will render it for you client-side with no
programming required. Otherwise you'll have to use dynamic SQL, which
is not much fun.

Yes. My Big Problem is indeed the Reporting tool which is Custom Reporting
tool of an ERP software that simply translate
the resulted query to a GUI look alike Report.
Unfortunately , There's Nothing I can do about it.

What are my options with dynamic sql ?

Show quoteHide quote

NO, your **first** problem is that you missed the basic concepts of
RDBMS.  A query returns a table; a table has a fixed number of columns.
A query is not a report; a query sends information to a front end
which can have a reporting tool in it.

This is definitions, not anything fancy.

\If you want to be a bad SQL programmer, this can be done with dynamic
SQL and a ton of procedural code that will be a bitch to maintain.

Selecting when Dates in where clause
"If you want a lot of information, one has to use cursors."
How to get variable number of rows on one row.
Returning a value from query
slow Select query specifying daterange using variables.