我需要在我的VB.net程序中创建一个CrossTab查询，该查询将生成此表 [英] I need to create a CrossTab Query in my VB.net program that will produce this table
问题描述
我目前在MS Access中有一个名为Quarterly_Growth_Rates的查询，该查询生成下表:
I currently have a query in MS Access named Quarterly_Growth_Rates which produces the table below:
Ticker Year Qtr Qtr_Growth
AAPL 2013 3 21.46
AMZN 2013 3 12.59
BBBY 2013 3 4.11
GOOG 2013 3 0.04
V 2013 3 5.13
AAPL 2013 2 -10.27
AMZN 2013 2 4.01
BBBY 2013 2 10.98
GOOG 2013 2 10.74
V 2013 2 7.66
AAPL 2013 1 -20.07
AMZN 2013 1 4.07
BBBY 2013 1 14
GOOG 2013 1 10.39
V 2013 1 10.17
我需要在将生成此表的VB.net程序中创建一个CrossTab查询:
I need to create a CrossTab Query in my VB.net program that will produce this table:
Ticker 2013-3 2013-2 2013-1
AAPL 21.46 -10.27 -20.07
AMZN 12.59 4.01 4.07
BBBY 4.11 10.98 14
GOOG 0.04 10.74 10.39
V 5.13 7.66 10.17
因此，该表现在显示列:行情收录器，年，Qtr和Qtr_Growth，每行每一年的每一季度.
So right now the table shows columns: Ticker, Year, Qtr, and Qtr_Growth a row for every quarter for every year of every ticker.
我需要显示行情栏，2013-1、2012-4、2012-3，每个行情栏仅显示一行.
I need it to show columns Ticker, 2013-1, 2012-4, 2012-3 and only one row for every ticker.
到目前为止，我的程序中已有以下代码:
So far I have this code in my program:
Dim cmd4 As OleDbCommand = New OleDbCommand("SELECT Ticker FROM Quarterly_Growth_Rates GROUP BY Ticker PIVOT Qtr ", Nordeen_Investing_3.con)
cmd4.ExecuteNonQuery()
此MS Access CrossTab查询的正确SQL语句是什么?
What is the correct SQL statement for this MS Access CrossTab query?
推荐答案
使用交叉表查询，这样就无需硬线连接" 年和Access SQL中的 Qtr 值.
Use a cross tab query so you don't need to "hard-wire" Year and Qtr values in your Access SQL.
我还认为， ExecuteNonQuery 是从 SELECT 查询中检索数据的错误方法.但是，这只是在此任务的VB.Net方面，我只能为您提供Access SQL的帮助.
Also I think ExecuteNonQuery is the wrong method to retrieve data from a SELECT query. But, that's on the VB.Net side of this task, and I can only help you with Access SQL.
我将来自您的查询源的样本数据存储在名为 YourQuery 的表中.
I stored the sample data from your query source in a table named YourQuery.
在Access 2007中，下面的第一个查询为我提供了此结果集:
In Access 2007, the first query below gave me this result set:
Ticker 2013-1 2013-2 2013-3
AAPL -20.07 -10.27 21.46
AMZN 4.07 4.01 12.59
BBBY 14 10.98 4.11
GOOG 10.39 10.74 0.04
V 10.17 7.66 5.13
仅在列顺序上与请求的输出不同.如果要按时间段降序排列列，请使用第二个查询.
It differs from your requested output only in the column order. If you want columns in descending time period order, use the second query.
还请注意，我的数据源是一个表，但您的是一个查询.我提到了这种差异，因为在构建交叉表时，可以通过从查询的源表开始，找到一种更直接的方式来获取所需的信息.
Note also my data source is a table, but yours is a query. I mentioned that difference because it's possible there might a more direct way to get what you need by starting from your query's source table(s) when building the cross tab.
TRANSFORM First(y.Qtr_Growth) AS FirstOfQtr_Growth
SELECT y.Ticker
FROM YourQuery AS y
GROUP BY y.Ticker
PIVOT y.Year & '-' & y.Qtr;
TRANSFORM First(y.Qtr_Growth) AS FirstOfQtr_Growth
SELECT y.Ticker
FROM YourQuery AS y
GROUP BY y.Ticker
ORDER BY y.Year & '-' & y.Qtr DESC
PIVOT y.Year & '-' & y.Qtr;
这篇关于我需要在我的VB.net程序中创建一个CrossTab查询，该查询将生成此表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
