根据未知行向 Access 2003 中的查询添加列

发布于 2024-11-07 23:21:43 字数 553 浏览 0 评论 0原文

我在 Access 2003 中有这样的查询：

col1 col2 col3 col4 
111  123  001  12.5 
111  123  002  12.5 
111  123  003  12.5
111  123  004  12.5
---------------------
112  211  001  5.2
112  211  002  5.2
112  211  003  5.2

我想做的是移动列中以 col3 开头的行并取消重复项，我的意思是我希望最终像这样：

col1 col2 new_col3 new_col4 new_col5......(unknown # of columns)
111  123  001      002      003       004   005.................. 12.5 
 (here I don't want a sum like in the crosstab query).

我尝试了很多方法，但是我什么也没得到。拜托，我真的需要你的帮助。先感谢您。

原文

I have this query in Access 2003:

col1 col2 col3 col4 
111  123  001  12.5 
111  123  002  12.5 
111  123  003  12.5
111  123  004  12.5
---------------------
112  211  001  5.2
112  211  002  5.2
112  211  003  5.2

What I'm trying to do is to move those rows starting with col3 in columns and suprime the duplicates, I mean I want to have in the end like this:

col1 col2 new_col3 new_col4 new_col5......(unknown # of columns)
111  123  001      002      003       004   005.................. 12.5 
 (here I don't want a sum like in the crosstab query).

I tried in so many ways, but I didn't get anything. Please, I really need your help.
Thank you in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

誰認得朕 2024-11-14 23:21:43

目前尚不清楚您想要新列和行的交叉点是什么。使用您提供给我们的少量示例输出，我们假设您想要 col3/col4 值本身。要交叉制表多个列，您需要使用多个查询。

Query1:
TRANSFORM Min(T1.col3) AS MinOfcol3
SELECT T1.col1, T1.col2
FROM Table1 AS T1
GROUP BY T1.col1, T1.col2
PIVOT T1.col3;

Query2:
TRANSFORM Min(Table1.col4) AS MinOfcol4
SELECT Table1.col1, Table1.col2
FROM Table1
GROUP BY Table1.col1, Table1.col2
PIVOT Table1.col4;

Query3:
SELECT Query1.*, Query2.*
FROM Query1 
    INNER JOIN Query2 
        ON (Query1.col2 = Query2.col2) 
            AND (Query1.col1 = Query2.col1);

如果 col3 和 col4 中的值已知，那么另一种方法是：

Select col1, col2
    , Min( IIF( col3 = '001', col3, NULL ) ) As col3_001
    , Min( IIF( col3 = '002', col3, NULL ) ) As col3_002
    , Min( IIF( col3 = '003', col3, NULL ) ) As col3_003
    , Min( IIF( col3 = '004', col3, NULL ) ) As col3_004
    , Min( IIF( col4 = 12.5, col4, NULL ) ) As col4_12_5
    , Min( IIF( col4 = 5.2, col4, NULL ) ) As col4_5_2
From Table1
Group By col1, col2

现在，我们真的应该首先问一下为什么要这样做。为什么不返回多行并以这种方式处理信息呢？

It isn't clear what you want at the intersection of the new columns and the rows. Using what little sample output you gave us, let's assume you want the col3/col4 values themselves. To crosstab multiple columns, you need to use multiple queries.

Query1:
TRANSFORM Min(T1.col3) AS MinOfcol3
SELECT T1.col1, T1.col2
FROM Table1 AS T1
GROUP BY T1.col1, T1.col2
PIVOT T1.col3;

Query2:
TRANSFORM Min(Table1.col4) AS MinOfcol4
SELECT Table1.col1, Table1.col2
FROM Table1
GROUP BY Table1.col1, Table1.col2
PIVOT Table1.col4;

Query3:
SELECT Query1.*, Query2.*
FROM Query1 
    INNER JOIN Query2 
        ON (Query1.col2 = Query2.col2) 
            AND (Query1.col1 = Query2.col1);

If the values in col3 and col4 are known, then another way to do it would be:

Select col1, col2
    , Min( IIF( col3 = '001', col3, NULL ) ) As col3_001
    , Min( IIF( col3 = '002', col3, NULL ) ) As col3_002
    , Min( IIF( col3 = '003', col3, NULL ) ) As col3_003
    , Min( IIF( col3 = '004', col3, NULL ) ) As col3_004
    , Min( IIF( col4 = 12.5, col4, NULL ) ) As col4_12_5
    , Min( IIF( col4 = 5.2, col4, NULL ) ) As col4_5_2
From Table1
Group By col1, col2

Now with all that, we really should ask why on Earth you want to do this in the first place. Why not return multiple rows and process the information that way?

回复收藏 0 原文

~没有更多了~