时间戳

首页 > 解决方案 > 关联/透视布尔列

sql - 关联/透视布尔列

问题描述

我有一张表格attendance，列出了 4 个象限代表的会议日期和出席情况：

Date     N S E W
1/1/2021 T T T T
1/3/2021 F T T T
1/4/2021 F F T T

等等

我想要的是找到各个代表彼此在同一个房间的最后日期：

  N          S            E                W
N 1/1/2021   1/1/2021     1/1/2021        1/1/2021
S 1/1/2021   1/3/2021     1/3/2021        1/3/2021
E 1/1/2021   1/3/2021     1/3/2021        1/4/2021
W 1/1/2021   1/3/2021     1/4/2021        1/4/2021

我一直在尝试使用 TRANSFORM/cross-tab，但我认为我需要 PIVOT 更长的时间才能做到这一点，而且 PIVOT 在 Access 中似乎不可能。

我很想听听对此的建议或方法。

标签： sqlms-accesspivot

解决方案

嗯。. . 您可以取消透视和重新聚合。这在 MS Access 中很痛苦，但您可以使用：

select t1.which,
       max(iif(t2.which = 'N' then date end) as N,
       max(iif(t2.which = 'S' then date end) as S,
       max(iif(t2.which = 'E' then date end) as E,
       max(iif(t2.which = 'W' then date end) as W
from (select date, 'N' as which from t where N = 'T' union all
      select date, 'S' from t where S = 'T' union all
      select date, 'E' from t where E = 'T' union all
      select date, 'W' from t where W = 'T'
     ) as t1 inner join
     (select date, 'N' as which from t where N = 'T' union all
      select date, 'S' from t where S = 'T' union all
      select date, 'E' from t where E = 'T' union all
      select date, 'W' from t where W = 'T'
     ) as t2
     on t1.date = t2.date
group by t1.which;

另一种方法对 4 个人来说并没有那么糟糕，但它不能很好地扩展：

select which, max(N) as n, max(S) as s, max(E) as e, max(W) as w
from (select N as which,
             date as N,
             iif(S = 'T', date, null) as S,
             iif(E = 'T', date, null) as E,
             iif(W = 'T', date, null) as W
      from t
      union all
      select S,
             iif(N = 'T', date, null) as N,
             date as S,
             iif(E = 'T', date, null) as E,
             iif(W = 'T', date, null) as W
      from t
      union all
      select S,
             iif(N = 'T', date, null) as N,
             iif(S = 'T', date, null) as S,
             date as E,
             iif(W = 'T', date, null) as W
      from t
      union all
      select S,
             iif(N = 'T', date, null) as N,
             iif(S = 'T', date, null) as S,
             iif(E = 'T', date, null) as E,
             date as W,
      from t
     ) as tt
group by which;

推荐阅读