Microsoft Access压缩表中的多行

Question

我在MS Access 2007中有一个问题，我希望有人有答案。我有一个很长但很简单的表，其中包含客户名称以及交付周的日期。我想通过将名称和所有日期列入一个新字段“ALLDays”，同时仍然保留所有数据来总结此表。源表看起来像这样：Name         Day  CustomerA    Monday  CustomerA    Thursday  CustomerB    Tuesday  CustomerB    Friday  CustomerC    Wednesday  CustomerC    Saturday  我想有一个返回如下结果的查询：Name         ALLDays  CustomerA    Monday, Thursday  CustomerB    Tuesday, Friday  CustomerC    Wednesday, Saturday  谢谢。

Answer 1

由于这只是一小部分选项，另一种没有VBA的方法是设置一系列IIF语句并连接结果。

SELECT name, 

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ") AS AllDays

FROM Table1

GROUP BY name

如果你是一个完美主义者，你甚至可以摆脱这样的最后一个逗号

SELECT name, 

LEFT(

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, "),

LEN(

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ")

) - 2

)

AS AllDays

FROM Table1

GROUP BY name

您还可以考虑将它们保存在单独的列中，因为如果从另一个列访问此查询，这可能会更有用。例如，通过这种方式查找只有星期二的实例会更容易。就像是：

SELECT name, 

IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday") AS Monday,  

IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday") AS Tuesday,

IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday") AS Wednesday,

IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday") AS Thursday,

IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday") AS Friday,

IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday") AS Saturday,

IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday") AS Sunday

FROM Table1

GROUP BY name