Solved

How to chart out results of a SQL Statement in VBscript

Posted on 2008-10-15
2
158 Views
Last Modified: 2010-04-21
I have a SQL Statement (thanks to a member here) that works quite nicely and returns the proper information I need but I do not know how to output it for users to see. I am using ASP and VBSCRIPT to create a webpage with a table. The table needs to contain the ifo from the SQL statement.

This SQL statement below returns the percentages of Invoices paid for each of our accounts by week (BDATE), when this is run I get a table with ACCO, PourcentageNet and BDATE for each account sorted by Account and BDATE, each account can have up to 52 BDATES (each BDATE is a weekending date).

I need to set up a table of 53 columns, column 1 is Account and column 2-53 are the BDATES.
Each row would have each account and the corresponding PercentValue for that BDATE as queried by the SQL below.

I don't know how to do this...

SELECT Payments.ACCO, Sum(IIf(((Payments.BSTA)="PAID"),Payments.Net,0))/Sum(Payments.Net) AS PourcentageNet, Payments.BDATE
FROM Payments
GROUP BY Payments.ACCO, Payments.BDATE
0
Comment
Question by:EGormly
2 Comments
 
LVL 5

Accepted Solution

by:
PaulKeating earned 500 total points
ID: 22726010
What you want is a crosstab. SQL can't do crosstabs. A select statement can produce any number of rows from 0 up at runtime, but the number and identity and data source of the columns is fixed at parse time. You want the columns in the query to depend on what is in the data and that is something standard SQL just can't do.

There are nonstandard extensions to SQL that will do it, but that depends on the dialect of SQL. For example, if your underlying database is SQL 2005 you can use a pivot clause. But since you're using iif() I am guessing your underlying database is Access, so you're probably out of luck there.

But if I am right and it is Access, then you might be able to save the data from your query in a temporary table and get Access to do an Access pivot table using VB Script.

I'm not an Access expert: interactively you do it with View | Pivot Table View. You might want to repost this question as an Access problem.

Just don't expect to be able to do it in SQL.
0
 

Author Closing Comment

by:EGormly
ID: 31506368
thanks.. not what I wanted to hear.. but still :>
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question