Re: Querying a dataset
- From: david <david@xxxxxxxxxxxxxxxx>
- Date: Tue, 06 Sep 2005 15:56:45 -0700
On 2005-09-06, Fred <leavemealone@home> wrote:
> I want to use a dataset so that I can obtain data from a number of sources
> and put it into one table. Using dataadaptors this seems to work well.
>
> Now I have a table (Forecast) in the dataset with columns:
> Rep, Area, Period, Qty.
>
> I want to display this data on a datagrid like:
> Rep, Period1_Qty, Period2_Qty, .. Period7_Qty
>
> where the period1_Qty is the sum of the Qtys for that Rep and for a
> particular period.
> I can do this in SQL by:
Two thoughts. First, there's a product that purports to do this at:
http://www.queryadataset.com. I've never looked at it myself, so I
can't promise anything. I'd be curious to know how it worked for you
though, since that's always seemed like a useful thing to have (and
possibly a fun project to write).
Secondly, just glancing at your sql statement. If you merged your
tables together into a single datatable could you get what you
want with filter and expressions? It depends on your data, and I can't
tell by looking at the example.
Even if you couldn't, it looks like you could get each table
individually without too much effort and then merge them together
at the end, thus sort of emulating the unions with code.
> SELECT Rep, SUM(Qty1), SUM(Qty2), ... SUM(Qty7) FROM
> (SELECT F1.Rep, SUM(F1.Qty) AS Qty1, 0 AS Qty2, ..., 0 AS Qty7
> FROM Forecast F1 WHERE F1.Period=1 GROUP BY F1.Rep
> UNION ALL
> SELECT F2Rep, 0 as Qty1, SUM(F2.Qty) AS Qty2, 0 AS Qty3, ...,0 AS Qty7
> FROM Forecast F2 WHERE F2.Period=2 GROUP BY F2.Rep
> UNION ALL
> ...
> UNION ALL
> SELECT F7.Rep, 0 AS Qty1, 0 AS Qty2, ... , SUM(F1.Qty) AS Qty7
> FROM Forecast F7 WHERE F7.Period=2 GROUP BY F7.Rep)
> GROUP BY Rep
>
> The above statement would vary depending on which periods to display or
> whther to display by Rep or Area.
> How can I create a table/view by applying a SQL statement like this to a
> Dataset?
>
> Thanks
> Fred
>
>
>
>
>
>
>
.
- References:
- Querying a dataset
- From: Fred
- Querying a dataset
- Prev by Date: Calling an Excel macro from VB.NET
- Next by Date: VB.NET Very Slow
- Previous by thread: Re: Querying a dataset
- Next by thread: Cursor position in RichEdit
- Index(es):
Relevant Pages
|
