SQL SSRS在分组时组合来自多行的列内的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22153473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSRS Combining values within columns from multiple rows when grouped
提问by dcdeez
I feel like this should be relatively easy to do in a SSRS report. Using VS 2010. I have a table that comes in from a basic sql query. Just dropping the columns into the a table in visual studio. I want to group the table by company first, which I do via the row group properties. I have a table that looks like this.
我觉得这在 SSRS 报告中应该比较容易做到。使用 VS 2010。我有一个来自基本 sql 查询的表。只需将列放入 Visual Studio 中的表中即可。我想首先按公司对表进行分组，这是通过行组属性进行的。我有一张看起来像这样的桌子。
Company Contact ContactSub SubCert Year
Bank3 Joey Steven.B A 2010
Bank2 Dave James A 2010
Bank2 Dave Steve B 2010
Bank2 Dave Mark B 2010
Bank2 Dave James A 2011
Bank2 Dave Steve A 2011
Bank2 Dave Mark B 2011
Bank2 Dave James A 2012
Bank2 Dave Steve A 2012
Bank2 Dave Mark A 2012
I now want to combine the Contact Subs and their subcert joined into one row. BUT only using the most recent year. Because some ContactSub may have had their SubCert upgraded to an A from a B.
我现在想将 Contact Subs 和他们的 subcert 合并成一行。但只使用最近的一年。因为某些 ContactSub 可能已将其 SubCert 从 B 升级到 A。
Company Contact ContactSub SubCert Year
Bank3 Joey Steven.B A 2010
Bank2 Dave James,Steve,Mark A,A,A 2012
I added an additional gorup by property, the "Year" column to the row and used this formula for the ContactSub and SubCert columns in the table:
我按属性添加了一个附加组，即行的“Year”列，并将此公式用于表中的 ContactSub 和 SubCert 列：
=Join(LookupSet(Fields!Company.Value,Fields!Company.Value,Fields!SubCert.Value,"DataSet Name"),",")
But this returned me:
但这使我返回：
Company Contact ContactSub SubCert Year
Bank3 Joey Steven.B A 2010
Bank2 Dave James,Steve,Mark,James A,B,B,A, 2012
Steve,Mark,James, Steve A,B,A,A,
Mark A
How could I clarify my formula to make it say for only the newest year instead of using the values for all years?
我怎么能澄清我的公式，让它只说最新的一年，而不是使用所有年份的值？
Hope this makes sense.
希望这是有道理的。
回答by Ian Preston
With your data:
使用您的数据：
And a table grouped on Company:
和分组的表Company：
I use the following expressions:
我使用以下表达式：
ContactSub
联系方式
=Join(LookupSet(Fields!Company.Value & Max(Fields!Year.Value)
, Fields!Company.Value & Fields!Year.Value
, Fields!ContactSub.Value
, "DataSet1"), ",")
SubCert
子证书
=Join(LookupSet(Fields!Company.Value & Max(Fields!Year.Value)
, Fields!Company.Value & Fields!Year.Value
, Fields!SubCert.Value
, "DataSet1"), ",")
You can see I'm using Max(Fields!Year.Value)as well as Fields!Company.Valueto only match on the highest year in the LookupSetexpression.
您可以看到我正在使用Max(Fields!Year.Value)以及Fields!Company.Value仅匹配LookupSet表达式中最高的年份。
This gives the required results:
这给出了所需的结果：
回答by kyzen
Your problem is that it's working as intended - the LOOKUPSET() function is returning allrecords from your dataset where the Company matches. You need to either tighten your criteria in your use of the LOOKUPSET() function, or add some custom code to go through the returned array and purge duplicates.
您的问题是它按预期工作 - LOOKUPSET() 函数从您的数据集中返回公司匹配的所有记录。您需要在使用 LOOKUPSET() 函数时收紧标准，或者添加一些自定义代码来遍历返回的数组并清除重复项。
One option for tightening up the lookup might be to add a calculated field to your dataset that concatenates the Company name and the Year together, which, at least looking at your sample data, would provide the slightly more unique key you're looking for.
加强查找的一种选择可能是向您的数据集中添加一个计算字段，该字段将公司名称和年份连接在一起，至少在查看您的示例数据时，这将提供您正在寻找的稍微更独特的键。
