by Tom Morea » Tue, 08 Jun 2004 05:10:22
hanx, again. You may be interested in fellow MVP Erland Sommarskog's
My book covers pivot tables (Ch 4, pp 116 - 118), but if you need to have
variable numbers of columns, and if you are committed to doing this entirely
within T-SQL, then you're looking at dynamic SQL. SQL Server doesn't
support arrays - you should be thinking tables, not arrays. As long as the
number of columns is fixed, the pivot table approach I gave you will be
sufficient. Just change the start and end dates. You will, as you noted,
get the same column names every time. However, a good client tool, such as
Reporting Services, or any front-end programming language like VB can
translate the column headers to anything you like. (ASP can do that.)
Indeed, this is part of the role of the front end.
Another idea that came to mind - if you need more than 7 days - is to
include the week number in the output and simply limit yourself to 7 days
*per row*. This way, if you're covering a 2-week period, you'll get two
rows per store - one for each week. Just something to keep in mind.
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
"jj" < XXXX@XXXXX.COM > wrote in message
news:rTGwc.624$ XXXX@XXXXX.COM ...
Tom Moreau wrote:
Sounds great. I just hope my company decides to buy it. They tend to
wait a few years after a new product is introduced before they start
By the way, I was able to get the pivot-like query to work. At first, I
could only get it to work for a 7-day (or any XX-day period, but it had
to be for a specific number of days.
Then, I got it to work for any date range, as long as it didn't exceed
whatever number of days I decided to limit the program to. I was able
to do this, by putting a CF (ColdFusion....I use that versus ASP) array
and loop in the QUERY's SELECT statement that returns the "AS Day1, AS
Day2, AS Day3, etc..) up to the number of days in the datediff()
function which takes the user's start/end dates as parameters.
Used another CF array and loop to get the column headers to print the
exact/correct date above each column.
But had a little problem with printing the output results...in getting
the query variables: Day1, Day2, etc.. to resolve. Was finally able to
do it, but the code is not as efficient as I'd like. I would still
prefer to allow the program to determine the # of days to process,
without any limits (although for screen-space sake, I would put a limit
of probably 31 days on this particular report).
I was hoping I could use an SQLServer array versus a ColdFusion array in
the QUERY. And use a DECLARE statement to create a temp variable for
the start of the array index, and use a WHILE loop to process the "As
Day1, As Day2, etc." values.
But, I couldn't find anything about arrays in your book, or in the
book's TOC or index. SQL Server Books Online didn't seem to have much
on arrays, other than in user-defined functions. I think if I could get
my "As Day1, As Day2, As Day3, etc.." query variables stored in an array
(e.g., As Day[i]), I might have an easier time of resolving the
variables when outputting the query results to a report, using a LOOP.
Oh well. Will keep at it. At least I was able to get it working well
enough to soo