当没有值时，如何强制交叉制表符显示行？ [英] How to force Cross tab to show row when no value?

问题描述

让我们说，我有一个像这样的结构表：


tblExpenses

===========

uid autonumber（key）

ExpenseArea text

ExpenseType text

年份数

价值号码



以下帖子：


uid ExpenseArea ExpenseType年份值

=== =========== =========== ==== =====

001 Gynger Investering 2010 15

002 Gynger Investering 2011 10

003 Gynger Investering 2012 8

004 Gynger Investering 2013 12

005 Gynger Investering 2014 13

006 Gynger Drift 2010 2

007 Gynger Drift 2011 2

008 Gynger Drift 2012 3

009 Gynger Drift 2013 2 br />
010 Gynger Drift 2014 3

011 Karuseller Drift 2010 12

012 Karuseller Drift 2011 12

013 Karuseller Drift 2012 13

014 Karuseller Drift 2013 12

015 Karuseller Drift 2014 13



使用以下查询我得到每年的总和数据。


TRANSFORM Sum（tblExpenses.Value）AS SumOfValue

SELECT tblExpenses。[ExpenseArea]，tblExpenses。[ExpenseType]

FROM tblExpenses

GROUP BY tblExpenses。[ExpenseArea]，tblExpenses。[ExpenseType]

PIVOT tblExpenses。[年];


现在，正如您在数据集中看到的那样，''Karuseller''，'Investering''没有值，我的问题是，如果有的话是一种强制查询为此返回一行但没有任何值的方法？

有人认为我有一个在单独的表中列出的使用类型并使用嵌套的SQL（或者沿着这些方向的东西）强制它显示所描述的所有费用类型。


/ Soren

解决方案

您既可以将费用区域表与费用类型表交叉连接，然后将外部联接与费用表交叉，也可以创建一个包含所需组合的表，然后使用费用表进行外连接。

展开 | 选择 | Wrap | 行号

Parodux，


（会用英文写，以便其他人能够一起阅读）

我尝试了你的建议，但它仍然可以不要给我一个''Karuseller''，''Investering''。

我可能会遗漏一些东西吗？


/ S？ren

Let us say, that I have a table structured like this:

tblExpenses
===========
uid autonumber (key)
ExpenseArea text
ExpenseType text
Year Number
Value Number


With the following posts:

uid ExpenseArea ExpenseType Year Value
=== =========== =========== ==== =====
001 Gynger Investering 2010 15
002 Gynger Investering 2011 10
003 Gynger Investering 2012 8
004 Gynger Investering 2013 12
005 Gynger Investering 2014 13
006 Gynger Drift 2010 2
007 Gynger Drift 2011 2
008 Gynger Drift 2012 3
009 Gynger Drift 2013 2
010 Gynger Drift 2014 3
011 Karuseller Drift 2010 12
012 Karuseller Drift 2011 12
013 Karuseller Drift 2012 13
014 Karuseller Drift 2013 12
015 Karuseller Drift 2014 13


Using the below query I get the data summed for each year.

TRANSFORM Sum(tblExpenses.Value) AS SumOfValue
SELECT tblExpenses.[ExpenseArea], tblExpenses.[ExpenseType]
FROM tblExpenses
GROUP BY tblExpenses.[ExpenseArea], tblExpenses.[ExpenseType]
PIVOT tblExpenses.[Year];


Now, As you can see in the dataset, there are no values for ''Karuseller'', ''Investering'', and my question is, if there is some way of forcing the query to return a row for this but without any values?

One thought I had was to have the expensetypes listed in a separate table and using a nested SQL (or something along those lines) to force it to show all expensetypes as described.


/Soren

解决方案

You could either cross join an expensearea table with an expensetype table and then outer join that to expenses table or you could create a table with all the combinations you need and then outer join that with the expenses table.

Expand|Select|Wrap|Line Numbers

Parodux,

(will write in English in order for others to be able to read along)
I tried your suggestion, but it still does not give me a row for ''Karuseller'', ''Investering''.
Could I be missing something?

/S?ren

这篇关于当没有值时，如何强制交叉制表符显示行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！