Solved

How to chart out results of a SQL Statement in VBscript

Posted on 2008-10-15
2
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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…

738 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