Solved

How to chart out results of a SQL Statement in VBscript

Posted on 2008-10-15
2
157 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 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